Exchange Partition of *-List and *-Range Partition

1.Exchange Partition of *-List Partition

Here I took example of Range-List partitioning method.

SQL> CREATE TABLE LIST_COMP_PART
  2      (EMP_ID NUMBER,
EMP_NAME VARCHAR2(30),
AGE VARCHAR2(50),
DEPT_ID CHAR(3))
PARTITION BY RANGE(AGE) SUBPARTITION BY LIST(DEPT_ID) 
(PARTITION P_R1 VALUES LESS THAN(25)
(SUBPARTITION P_R1_L1 VALUES('SLS','ACC'),
 SUBPARTITION P_R1_L2 VALUES('HRM','MAR')
),
PARTITION P_R2 VALUES LESS THAN(35)
(SUBPARTITION P_R2_L1 VALUES('SLS','ACC'),
 SUBPARTITION P_R2_L2 VALUES('HRM','MAR')
),
PARTITION P_R3 VALUES LESS THAN(MAXVALUE)
(SUBPARTITION P_R3_L1 VALUES('SLS','ACC'),
 SUBPARTITION P_R3_L2 VALUES('HRM','MAR')
));  3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18  

Table created.

SQL> CREATE TABLE LIST_SIMPLE_PART
    (EMP_ID NUMBER,
EMP_NAME VARCHAR2(30),
AGE VARCHAR2(50),
DEPT_ID CHAR(3))
PARTITION BY LIST (DEPT_ID)
(PARTITION P1 VALUES('SLS','ACC'),
 PARTITION P2 VALUES('HRM','MAR'));  2    3    4    5    6    7    8  

Table created.

SQL> ALTER TABLE LIST_COMP_PART EXCHANGE PARTITION P_R1 WITH TABLE LIST_SIMPLE_PART;

Table altered.

SQL> 

2.Exchange Partition of *-Range Partitioned table :

Here I took example of Range-Range partitioning method.

SQL> CREATE TABLE RANGE_RANGE_COMP
(ID NUMBER,
NAME VARCHAR2(50),
REGISTER_DT DATE,
JOINING_DT DATE)
PARTITION BY RANGE (REGISTER_DT)
SUBPARTITION BY RANGE(JOINING_DT)
(PARTITION P_RR1 VALUES LESS THAN (TO_DATE('01-JAN-2019','dd-MON-yyyy'))
 (SUBPARTITION P_RR1_R1 VALUES LESS THAN (TO_DATE('31-JAN-2019','dd-MON-yyyy')),
 SUBPARTITION P_RR1_R2 VALUES LESS THAN (TO_DATE('28-FEB-2019','dd-MON-yyyy')),
 SUBPARTITION P_RR1_R3 VALUES LESS THAN (TO_DATE('31-MAR-2019','dd-MON-yyyy'))
  2    3    4    5    6    7    8    9   10   11   ),
 PARTITION P_RR2 VALUES LESS THAN (TO_DATE('01-APR-2019','dd-MON-yyyy'))
 (SUBPARTITION P_RR2_R1 VALUES LESS THAN (TO_DATE('30-APR-2019','dd-MON-yyyy')),
 SUBPARTITION P_RR2_R2 VALUES LESS THAN (TO_DATE('31-MAY-2019','dd-MON-yyyy')),
 SUBPARTITION P_RR2_R3 VALUES LESS THAN (TO_DATE('30-JUN-2019','dd-MON-yyyy'))
 ));
  12   13   14   15   16   17  
Table created.

SQL> CREATE TABLE RANGE_SIMPLE
  2  (ID NUMBER,
NAME VARCHAR2(50),
REGISTER_DT DATE,
JOINING_DT DATE)
PARTITION BY RANGE(JOINING_DT)
(PARTITION P1 VALUES LESS THAN (TO_DATE('31-JAN-2019','dd-MON-yyyy')),
 PARTITION P2 VALUES LESS THAN (TO_DATE('28-FEB-2019','dd-MON-yyyy')),
 PARTITION P3 VALUES LESS THAN (TO_DATE('31-MAR-2019','dd-MON-yyyy'))
);  3    4    5    6    7    8    9   10  

Table created.

SQL> ALTER TABLE RANGE_RANGE_COMP EXCHANGE PARTITION P_RR1 WITH TABLE RANGE_SIMPLE;

Table altered.

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: Joel Perez’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp