Exchage PartitionOracle

Move Table Partition between Tables

I was recently asked if we can move partition of one table into another? Basically a developers had truncated the partition and the team wanted to move the partition from one table to another.Both the table were structurally the same and had same data.
Here I will be showing how we can do that
1. Create a monthly partitioned table –

SQL> create table sales_part
    (product char(25),channel_id number,cust_id number,
    amount_sold number, time_id date)
    partition by range (time_id)
    (
    partition sale_jan values less than (to_date('01-02-2013','dd-mm-yyyy')),
    partition sale_feb values less than (to_date('01-03-2013','dd-mm-yyyy')),
    partition sale_mar values less than (to_date('01-04-2013','dd-mm-yyyy')),
    partition sale_apr values less than (to_date('01-05-2013','dd-mm-yyyy')),
    partition sale_may values less than (to_date('01-06-2013','dd-mm-yyyy')),
    partition sale_jun values less than (to_date('01-07-2013','dd-mm-yyyy')),
    partition sale_jul values less than (to_date('01-08-2013','dd-mm-yyyy')),
    partition sale_aug values less than (to_date('01-09-2013','dd-mm-yyyy')),
    partition sale_sep values less than (to_date('01-10-2013','dd-mm-yyyy')),
    partition sale_oct values less than (to_date('01-11-2013','dd-mm-yyyy')),
    partition sale_nov values less than (to_date('01-12-2013','dd-mm-yyyy')),
    partition sale_dec values less than (to_date('01-01-2014','dd-mm-yyyy'))
   ) TABLESPACE DEMO
 19  ;
Table created.

2. Insert some data into the table

SQL> insert into sales_part
    select
    'Oracle Enterprise Edition' as product,
     mod(rownum,5) as channel_id,
     mod(rownum,1000) as cust_id ,
     5000 as amount_sold,
     to_date
     ('01.' || lpad(to_char(mod(rownum,6)+4),2,'0') || '.2013' ,'dd.mm.yyyy')
     as time_id
     from dual connect by level commit;
Commit complete.

3. Create Index

SQL> create index idx_sale_local on sales_part (cust_id,time_id)local tablespace DEMO;
Index created.
SQL>
SQL> create index idx_sale_global on sales_part (channel_id)  tablespace DEMO;
Index created.

4. Gathers stats

SQL> exec dbms_stats.gather_table_stats('ANAND','SALES_PART', cascade => true);
PL/SQL procedure successfully completed.
Elapsed: 00:00:08.56
SQL> @partition_table
Enter value for table_name: SALES_PART
old  12: WHERE  a.table_name  = UPPER('&table_name')
new  12: WHERE  a.table_name  = UPPER('SALES_PART')
Enter value for owner: ANAND
old  13: AND    a.table_owner = Upper('&owner')
new  13: AND    a.table_owner = Upper('ANAND')
TABLE_NAME      PARTITION_NAME            HIGH_VALUE                LAST_ANAL TABLESPACE_NAME                INITIAL_EXTENT NEXT_EXTENT PCT_INCREASE   NUM_ROWS AVG_ROW_LEN
--------------- ------------------------- ------------------------- --------- ------------------------------ -------------- ----------- ------------ ---------- -----------
.....................
.....................
SALES_PART      SALE_APR                  TO_DATE(' 2013-05-01 00:0 14-AUG-15 DEMO                                  8388608     1048576                  165000          44
                                          0:00', 'SYYYY-MM-DD HH24:
                                          MI:SS', 'NLS_CALENDAR=GRE
                                          GORIAN')
SALES_PART      SALE_MAY                  TO_DATE(' 2013-06-01 00:0 14-AUG-15 DEMO                                  8388608     1048576                  165000          44
                                          0:00', 'SYYYY-MM-DD HH24:
                                          MI:SS', 'NLS_CALENDAR=GRE
                                          GORIAN')
SALES_PART      SALE_JUN                  TO_DATE(' 2013-07-01 00:0 14-AUG-15 DEMO                                  8388608     1048576                  165000          44
                                          0:00', 'SYYYY-MM-DD HH24:
                                          MI:SS', 'NLS_CALENDAR=GRE
                                          GORIAN')
SALES_PART      SALE_JUL                  TO_DATE(' 2013-08-01 00:0 14-AUG-15 DEMO                                  8388608     1048576                  165000          44
                                          0:00', 'SYYYY-MM-DD HH24:
                                          MI:SS', 'NLS_CALENDAR=GRE
                                          GORIAN')
SALES_PART      SALE_AUG                  TO_DATE(' 2013-09-01 00:0 14-AUG-15 DEMO                                  8388608     1048576                  165000          44
                                          0:00', 'SYYYY-MM-DD HH24:
                                          MI:SS', 'NLS_CALENDAR=GRE
                                          GORIAN')
SALES_PART      SALE_SEP                  TO_DATE(' 2013-10-01 00:0 14-AUG-15 DEMO                                  8388608     1048576                  165000          44
                                          0:00', 'SYYYY-MM-DD HH24:
                                          MI:SS', 'NLS_CALENDAR=GRE
                                          GORIAN')
......................
.....................
12 rows selected.

5. Lets create another table, exactly the same

SQL> create table sales_part_new
    (product char(25),channel_id number,cust_id number,
    amount_sold number, time_id date)
    partition by range (time_id)
    (
    partition sale_jan values less than (to_date('01-02-2013','dd-mm-yyyy')),
    partition sale_feb values less than (to_date('01-03-2013','dd-mm-yyyy')),
    partition sale_mar values less than (to_date('01-04-2013','dd-mm-yyyy')),
    partition sale_apr values less than (to_date('01-05-2013','dd-mm-yyyy')),
    partition sale_may values less than (to_date('01-06-2013','dd-mm-yyyy')),
    partition sale_jun values less than (to_date('01-07-2013','dd-mm-yyyy')),
    partition sale_jul values less than (to_date('01-08-2013','dd-mm-yyyy')),
    partition sale_aug values less than (to_date('01-09-2013','dd-mm-yyyy')),
    partition sale_sep values less than (to_date('01-10-2013','dd-mm-yyyy')),
    partition sale_oct values less than (to_date('01-11-2013','dd-mm-yyyy')),
    partition sale_nov values less than (to_date('01-12-2013','dd-mm-yyyy')),
    partition sale_dec values less than (to_date('01-01-2014','dd-mm-yyyy'))
   ) TABLESPACE DEMO
 19  ;
Table created.

6. Inserted the data using the same insert command,gathered the stats and created index as previously done in step 2,3 and 4.
7. Truncated one of the partition SALE_JUN, from SALES_PART_NEW

SQL> alter table SALES_PART_NEW truncate partition SALE_JUN;
Table truncated.

Now that the data is truncated from partition SALE_JUN of table SALES_PART_NEW, can we move same table partition of SALES_PART to SALES_PART_NEW.
We can use EXCHANGE PARTITION for the same
1. Create an interim table – Same structure without any partition

SQL> create table sales_part_interim
     (product char(25),
     channel_id number,
     cust_id number,
     amount_sold number,
     time_id date)
     TABLESPACE DEMO
    ;
Table created.
SQL> select count(1) from sales_part_interim;
  COUNT(1)
----------
         0

2. Perform Exchange Partition between Interim Table and SALE_JUN partition of SALES_PART.

SQL> alter table sales_part exchange partition SALE_JUN with table sales_part_interim;
Table altered.
Elapsed: 00:00:00.12
SQL> select count(1) from sales_part_interim;
  COUNT(1)
----------
    165000
SQL> @partition_table
Enter value for table_name: SALES_PART
old   8: WHERE  a.table_name  = UPPER('&table_name')
new   8: WHERE  a.table_name  = UPPER('SALES_PART')
Enter value for owner: ANAND
old   9: AND    a.table_owner = Upper('&owner')
new   9: AND    a.table_owner = Upper('ANAND')
TABLE_NAME      PARTITION_NAME            HIGH_VALUE                                              LAST_ANAL TABLESPACE_NAME                  NUM_ROWS
--------------- ------------------------- ------------------------------------------------------- --------- ------------------------------ ----------
SALES_PART      SALE_MAY                  TO_DATE(' 2013-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS 14-AUG-15 DEMO                               165000
                                          ', 'NLS_CALENDAR=GREGORIAN')

SALES_PART      SALE_JUN                  TO_DATE(' 2013-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS           DEMO
                                          ', 'NLS_CALENDAR=GREGORIAN')

SALES_PART      SALE_JUL                  TO_DATE(' 2013-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS 14-AUG-15 DEMO                               165000
                                          ', 'NLS_CALENDAR=GREGORIAN')
.................

3. Rebuild the global indexes on SALES_PART as it would be UNUSABLE.
4. Now the 2nd Exchange Partition to where INTERIM table will be exchanged with the Partition SALE_JUN of SALE_PART_NEW table

SQL> alter table sales_part_new exchange partition SALE_JUN with table  sales_part_interim;
Table altered.
Elapsed: 00:00:00.27
SQL> select count(1) from sales_part_interim;
  COUNT(1)
----------
         0
Elapsed: 00:00:00.01
SQL>
SQL> select count(*) from sales_part_new partition (sale_jun);
  COUNT(*)
----------
    165000
Elapsed: 00:00:00.03
SQL>

5. Check the index status and rebuild the required ones.

4 thoughts on “Move Table Partition between Tables

  1. Hi Anand,
    I am using FK referenced Interval Partition Table and I am getting below error while exchanging partition :
    alter table ORDERS_NEW EXCHANGE PARTITION SYS_P22051 WITH TABLE ORDERS_INTERIM;
    Error report:
    SQL Error: ORA-02266: unique/primary keys in table referenced by enabled foreign keys
    02266. 00000 – “unique/primary keys in table referenced by enabled foreign keys”
    *Cause: An attempt was made to truncate a table with unique or
    primary keys referenced by foreign keys enabled in another table.
    Other operations not allowed are dropping/truncating a partition of a
    partitioned table or an ALTER TABLE EXCHANGE PARTITION.
    *Action: Before performing the above operations the table, disable the
    foreign key constraints in other tables. You can see what
    constraints are referencing a table by issuing the following
    command:
    SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = “tabnam”;
    While trying to Disable FK from Child Tables it gives below error :
    Error report:
    SQL Error: ORA-14650: operation not supported for reference-partitioned tables
    Please suggest !!

  2. Thanks Sir!
    But my test case got failed with below error
    SQL> ALTER TABLE OWNER.MIS_REC_LOG_NEW EXCHANGE PARTITION P20150718 WITH TABLE OWNER.MIS_REC_LOG;
    ALTER TABLE OWNER.MIS_REC_LOG_NEW EXCHANGE PARTITION P20150718 WITH TABLE OWNER.MIS_REC_LOG
    *
    ERROR at line 1:
    ORA-14501: object is not partitioned

Leave a Reply