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.

3 thoughts on “Move Table Partition between Tables

  1. 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

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s