Oracle Partitions

 Setting up Environment:

 CREATE TABLE TXN

   (    TDATE DATE,

        START_DATE NUMBER,

        END_DATE NUMBER,

        PNUMBER NUMBER,

        ACODE CHAR(2)

   ) tablespace users;

begin

for z in 0..11 loop

for i in 1..323 loop

  for j in 1..31 loop

insert into txn values(sysdate-433+j+(31*z),1212*i,2323232*i,122323*i,(select

decode(trunc(dbms_random.value(1,5)),1,'CN',2,'MD',3,'CO',4,'TR','CN') from dual));

end loop;

end loop;

end loop;

end;

/

select table_name,num_rows,last_analyzed,degree,partitioned from user_tables;

Range Partitions

CREATE TABLE TXN_RANGE

(TDATE DATE,

        START_DATE NUMBER,

        END_DATE NUMBER,

        PNUMBER NUMBER,

        ACODE CHAR(2))

PARTITION BY RANGE (TDATE)

(PARTITION p1 VALUES LESS THAN (TO_DATE('31/03/2020', 'DD/MM/YYYY')) TABLESPACE USERS,

 PARTITION p2 VALUES LESS THAN (TO_DATE('30/06/2020', 'DD/MM/YYYY')) TABLESPACE USERS,

 PARTITION p3 VALUES LESS THAN (TO_DATE('30/09/2020', 'DD/MM/YYYY')) TABLESPACE USERS,

 PARTITION p4 VALUES LESS THAN (MAXVALUE) TABLESPACE USERS);

exec dbms_stats.gather_table_stats('','TXN_RANGE');

select table_name,partition_name,num_rows,last_analyzed from user_tab_partitions;

CREATE TABLE TXN_number

(tno number,

        sno NUMBER,

        Eno NUMBER,

        PNUMBER NUMBER,

        ACODE CHAR(2))

PARTITION BY RANGE (tno)

(PARTITION p1 VALUES LESS THAN (1000) TABLESPACE USERS,

 PARTITION p2 VALUES LESS THAN (2000) TABLESPACE USERS,

 PARTITION p3 VALUES LESS THAN (3000) TABLESPACE USERS,

 PARTITION p4 VALUES LESS THAN (MAXVALUE) TABLESPACE USERS);

begin

    for i in 1..4000 loop

    insert into TXN_number values(i,i,i,i,'CN');

    end loop;

    end;

    /

LIST Partition:

CREATE TABLE TXN_LIST

(TDATE DATE,

        START_DATE NUMBER,

        END_DATE NUMBER,

        PNUMBER NUMBER,

        ACODE CHAR(2))

PARTITION BY LIST (ACODE)

(PARTITION Chennai VALUES ('CN') TABLESPACE USERS,

PARTITION madurai VALUES ('MD') TABLESPACE USERS,

PARTITION Covai VALUES ('CO') TABLESPACE USERS,

PARTITION trichy VALUES ('TR') TABLESPACE USERS);

HASH Partition:

CREATE TABLE TXN_HASH

(TDATE DATE,

        START_DATE NUMBER,

        END_DATE NUMBER,

        PNUMBER NUMBER,

        ACODE CHAR(2)) 

PARTITION BY HASH (TDATE)

PARTITIONS 4

STORE IN (users, users, users, users);

 COMPOSITE Partition:

CREATE TABLE TXN_RANGE_LIST

(TDATE DATE,

        START_DATE NUMBER,

        END_DATE NUMBER,

        PNUMBER NUMBER,

        ACODE CHAR(2))

PARTITION BY RANGE (TDATE)

SUBPARTITION BY LIST (ACODE)

SUBPARTITION TEMPLATE

(

  SUBPARTITION Chennai VALUES ('CN'),

  SUBPARTITION madurai VALUES ('MD'),

  SUBPARTITION Coimb VALUES ('CO'),

  SUBPARTITION trichy VALUES ('TR')

  )

(PARTITION p1 VALUES LESS THAN (TO_DATE('31/03/2020', 'DD/MM/YYYY')) TABLESPACE USERS,

 PARTITION p2 VALUES LESS THAN (TO_DATE('30/06/2020', 'DD/MM/YYYY')) TABLESPACE USERS,

 PARTITION p3 VALUES LESS THAN (TO_DATE('30/09/2020', 'DD/MM/YYYY')) TABLESPACE USERS,

 PARTITION p4 VALUES LESS THAN (MAXVALUE) TABLESPACE USERS);

 

 

 INTERVAL Partition:

 CREATE TABLE TXN_INTERVAL_MONTH (TDATE DATE,

        START_DATE NUMBER,

        END_DATE NUMBER,

        PNUMBER NUMBER,

        ACODE CHAR(2))

PARTITION BY RANGE (TDATE)

INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))

(

   PARTITION FIRST_PARTITION values LESS THAN (TO_DATE('01/03/2020', 'DD/MM/YYYY'))

);

 

 INTERVAL Partition:

  CREATE TABLE TXN_INTERVAL_DAY (TDATE DATE,

        START_DATE NUMBER,

        END_DATE NUMBER,

        PNUMBER NUMBER,

        ACODE CHAR(2))

PARTITION BY RANGE (TDATE)

INTERVAL (NUMTODSINTERVAL(1,'DAY'))

(

   PARTITION FIRST_PARTITION values LESS THAN (TO_DATE('01/03/2020', 'DD/MM/YYYY'))

);

SYSTEM Partition 

 

 CREATE TABLE TXN_SYSTEM (TDATE DATE,

        START_DATE NUMBER,

        END_DATE NUMBER,

        PNUMBER NUMBER,

        ACODE CHAR(2))

PARTITION BY SYSTEM

(

  PARTITION part_1,

  PARTITION part_2,

  PARTITION part_3

);

REFERENCE  Partition

CREATE TABLE TXN_parent  (TDATE DATE primary key,

        START_DATE NUMBER,

        END_DATE NUMBER,

        PNUMBER NUMBER,

        ACODE CHAR(2))

PARTITION BY RANGE (TDATE)

(PARTITION p1 VALUES LESS THAN (TO_DATE('31/03/2020', 'DD/MM/YYYY')) TABLESPACE USERS,

 PARTITION p2 VALUES LESS THAN (TO_DATE('30/06/2020', 'DD/MM/YYYY')) TABLESPACE USERS,

 PARTITION p3 VALUES LESS THAN (TO_DATE('30/09/2020', 'DD/MM/YYYY')) TABLESPACE USERS,

 PARTITION p4 VALUES LESS THAN (MAXVALUE) TABLESPACE USERS);

  

 

CREATE TABLE txn_child (TDATE DATE,

        START_DATE NUMBER,

        END_DATE NUMBER,

        PNUMBER NUMBER,

        ACODE CHAR(2),

  CONSTRAINT child_parent_tab_fk FOREIGN KEY (tdate)

                               REFERENCES txn_parent (tdate)

)

PARTITION BY REFERENCE (child_parent_tab_fk);

CN,MD,CO,TR

VIRTUAL Partition

CREATE TABLE TXN_VIRTUAL(TDATE DATE,

        START_DATE NUMBER,

        END_DATE NUMBER,

        PNUMBER NUMBER,

        ACODE CHAR(2),

SECOND_CHAR

    GENERATED ALWAYS AS

      (

        UPPER(SUBSTR(TRIM(ACODE), 2, 1))

      ) VIRTUAL

)

PARTITION BY LIST (SECOND_CHAR)

(

  PARTITION part_A_J VALUES ('A','B','C','D','E','F','G'),

  PARTITION part_K_O VALUES ('H','I','J','K','L','M','N'),

  PARTITION part_P_T VALUES ('O','P','Q','R','S','T','U'),

  PARTITION part_V_Z VALUES ('V','W','X','Y','Z')

);

insert into TXN_VIRTUAL(tdate,start_date,end_Date,pnumber,acode) select * from txn;

OPERATIONS on Partitions:

Hints :

Add

modify  

alter table TXN_VIRTUAL modify partition PART_P_T drop  values('O');

drop

truncate

 

==============

rename

================

coalesce

alter table txn_hash coalesce;

alter table txn_hash

modify partition SYS_P829

coalesce partition;

  alter table txn_hash coalesce partition;

 

 

 

exchange

create  table TXN_LIST_1 as select * from txn_list  where 1=2;

alter table TXN_LIST

    exchange partition MADURAI

    with table TXN_LIST_1

    with validation;

 

 SPLIT

 

alter table txn_number split partition p1 at (500) into 

  (Partition p1_L500 tablespace users,

  Partition p1_M500 tablespace users);

MERGE 

 alter table txn_number

    merge partitions  p1_l500,p1_m500

    into partition p1;

select count(*) from txn_list_1;

=======================

online move

======================

cleanup

cleanup_online_op

CLEANUP_GIDX

execute dbms_part.cleanup_online_op('','TXN_LIST');

 cleans the failed alter table ..move command