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.
Learn. Share. Repeat. 
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
Please try the below command
ALTER TABLE OWNER.MIS_REC_LOG EXCHANGE PARTITION P20150718 WITH TABLE OWNER.MIS_REC_LOG_NEW;
Thanks Sir !
it work I was using wrong command moving from non partition to partition table. 🙂
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 !!