DB version : 11.2.0.4
OS : Oracle Linux 6.5
I have the below table name SALES_DTL in production DB. It is 200 GB in size. It is range (interval) partitioned by month.
As part of archiving strategy, I want to move partitions older than 3 months to another partitioned table (SALES_DTL_HIST) with the same table structure in the same schema.
As shown below , I managed to move a partition from source partitioned table to a Non-partitioned table . But, how can I move (exchange) a partition from one partitioned table to another partitioned table ?
ie. I want to move partitions from SALES_DTL table to the 'corresponding' partitions in SALES_DTL_HIST table shown at the bottom?
--- Source table
create table sales_dtl
(
txn_id number(9),
salesman_id number(5),
salesman_name varchar2(30),
sales_rgn varchar2(10),
sales_amount number(10),
sales_date date,
constraint pk_sales_dtl primary key (txn_id)
using index enable
)
partition by range (sales_date)
interval (numtoyminterval(1, 'month'))
(
partition sales_dec2016 values less than (to_date('2017-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')) ,
partition sales_jan2017 values less than (to_date('2017-02-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')) ,
partition sales_feb2017 values less than (to_date('2017-03-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')) ,
partition sales_mar2017 values less than (to_date('2017-04-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
) ;
insert into sales_dtl values (1, 348, 'John', 'FR' , 3892 , SYSDATE-30 ) ;
insert into sales_dtl values (2, 435, 'Katy', 'UK' , 257 , SYSDATE-65 ) ;
insert into sales_dtl values (3, 216, 'Charles', 'DE' , 163 , SYSDATE-80 ) ;
insert into sales_dtl values (4, 174, 'Stephen', 'NR' , 475 , SYSDATE ) ;
commit;
SQL> set lines 200
SQL> select * from sales_dtl;
TXN_ID SALESMAN_ID SALESMAN_NAME SALES_RGN SALES_AMOUNT SALES_DAT
---------- ----------- ------------------------------ ---------- ------------ ---------
2 435 Katy UK 257 16-JAN-17
3 216 Charles DE 163 01-JAN-17
1 348 John FR 3892 20-FEB-17
4 174 Stephen NR 475 22-MAR-17
--- Unpartitioned Target table
--- Moving partition to an Unpartitioned history table seems to be easy
create table sales_dtl_arch
(
txn_id number(9),
salesman_id number(5),
salesman_name varchar2(30),
sales_rgn varchar2(10),
sales_amount number(10),
sales_date date,
constraint pk_sales_dtl_arch primary key (txn_id) using index enable
);
SQL> alter table SALES_DTL exchange partition SALES_JAN2017 with table SALES_DTL_ARCH without validation update global indexes;
Table altered.
SQL> select * from sales_dtl;
TXN_ID SALESMAN_ID SALESMAN_NAME SALES_RGN SALES_AMOUNT SALES_DAT
---------- ----------- ------------------------------ ---------- ------------ ---------
1 348 John FR 3892 20-FEB-17
4 174 Stephen NR 475 22-MAR-17
---- But, I want to move partitions from SALES_DTL to the below mentioned partitioned SALES_DTL_HIST (rather than an unpartitioned table like above )
create table SALES_DTL_HIST
(
txn_id number(9),
salesman_id number(5),
salesman_name varchar2(30),
sales_rgn varchar2(10),
sales_amount number(10),
sales_date date,
constraint pk_sales_dtl_hist primary key (txn_id)
using index enable
)
partition by range (sales_date)
interval (numtoyminterval(1, 'month'))
(
partition sales_dec2016 values less than (to_date('2017-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')) ,
partition sales_jan2017 values less than (to_date('2017-02-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')) ,
partition sales_feb2017 values less than (to_date('2017-03-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')) ,
partition sales_mar2017 values less than (to_date('2017-04-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
) ;