Hi,

I have two Fat Tables, Both are having Composite RANGE-HASH partitions. Table has one Primary RANGE partitioned on Created_Date Column/ Monthly and 4 Subpartitions HASH on Primary Key Column. We have ~3.5 yrs+ data (42 Range Partition/168 Hash Subpartition).

Both the table contains 2 CLOB columns.
Table A has each Partiton average 8M Rows & 5M Blocks.
Table B has each Partiton average 10M Rows & 8M Blocks.

Table A structure look like-

CREATE TABLE TableA
(
  PROVIDER_REQUEST_LOG_ID      NUMBER(19)       NOT NULL, --> Primary Key
  FK_TRANSACTION_ID            NUMBER(19),
  FK_PROVIDER_REQUEST_TYPE_ID  NUMBER(19)       NOT NULL,
  FK_PROVIDER_ID               NUMBER(19)       NOT NULL,
  REQUEST_DATA                 CLOB,
  RESPONSE_DATA                CLOB,
  START_DATE                   TIMESTAMP(6),
  FINISH_DATE                  TIMESTAMP(6),
  CREATED_BY                   VARCHAR2(256 CHAR) NOT NULL,
  CREATED_DATE                 TIMESTAMP(6)     NOT NULL,
  LAST_MODIFIED_BY             VARCHAR2(256 CHAR) NOT NULL,
  LAST_MODIFIED_DATE           TIMESTAMP(6)     NOT NULL
)
TABLESPACE TESTD
PARTITION BY RANGE (CREATED_DATE)
SUBPARTITION BY HASH (PROVIDER_REQUEST_LOG_ID)
SUBPARTITION TEMPLATE
  (SUBPARTITION SP1 TABLESPACE TESTD,
   SUBPARTITION SP2 TABLESPACE TESTD,
   SUBPARTITION SP3 TABLESPACE TESTD,
   SUBPARTITION SP4 TABLESPACE TESTD
  ) 
(
.... 
PARTITION P_OCT
VALUES LESS THAN(TO_DATE('01/11/2016','DD/MM/YYYY')) TABLESPACE TEST1
PARTITION P_NOV
VALUES LESS THAN(TO_DATE('01/12/2016','DD/MM/YYYY')) TABLESPACE TEST2
PARTITION P_DEC
VALUES LESS THAN(TO_DATE('01/01/2017','DD/MM/YYYY')) TABLESPACE TEST3
PARTITION P_JAN
VALUES LESS THAN(TO_DATE('01/02/2017','DD/MM/YYYY')) TABLESPACE TEST4
PARTITION P_FEB
VALUES LESS THAN(TO_DATE('01/03/2017','DD/MM/YYYY')) TABLESPACE TEST5
.....

);


Table B structure look like-
CREATE TABLE TableB
(
  ROS_REQUEST_LOG_ID      NUMBER(19)            NOT NULL,  --> Primary Key
  FK_TRANSACTION_ID       NUMBER(19)            NOT NULL,
  REQUEST_DATA            CLOB,
  RESPONSE_DATA           CLOB,
  START_DATE              TIMESTAMP(6),
  FINISH_DATE             TIMESTAMP(6),
  CREATED_BY              VARCHAR2(256 CHAR)    NOT NULL,
  CREATED_DATE            TIMESTAMP(6)          NOT NULL,
  LAST_MODIFIED_BY        VARCHAR2(256 CHAR)    NOT NULL,
  LAST_MODIFIED_DATE      TIMESTAMP(6)          NOT NULL,
  FK_ROS_REQUEST_TYPE_ID  NUMBER(19)
)
TABLESPACE TESTD
PARTITION BY RANGE (CREATED_DATE)
SUBPARTITION BY HASH (ROS_REQUEST_LOG_ID)
SUBPARTITION TEMPLATE
  (SUBPARTITION SP1 TABLESPACE TEST1,
   SUBPARTITION SP2 TABLESPACE TEST1,
   SUBPARTITION SP3 TABLESPACE TEST1,
   SUBPARTITION SP4 TABLESPACE TEST1
  ) 
(
.... 
PARTITION P_OCT
VALUES LESS THAN(TO_DATE('01/11/2016','DD/MM/YYYY')) TABLESPACE TEST1
PARTITION P_NOV
VALUES LESS THAN(TO_DATE('01/12/2016','DD/MM/YYYY')) TABLESPACE TEST2
PARTITION P_DEC
VALUES LESS THAN(TO_DATE('01/01/2017','DD/MM/YYYY')) TABLESPACE TEST3
PARTITION P_JAN
VALUES LESS THAN(TO_DATE('01/02/2017','DD/MM/YYYY')) TABLESPACE TEST4
PARTITION P_FEB
VALUES LESS THAN(TO_DATE('01/03/2017','DD/MM/YYYY')) TABLESPACE TEST5
.....

);

Now I wanted to Archive those two tables in some other schema, and wanted to keep just 6 month of data....I tried CTAS for just 2 month, but it took more than 6 hours and not completed. unfortunately I had to kill it.

Here is what my query look like for CTAS (for TableA).

CREATE TABLE TABLE_TEMP_A
(
  PROVIDER_REQUEST_LOG_ID     NOT NULL,
  FK_TRANSACTION_ID,          
  FK_PROVIDER_REQUEST_TYPE_ID NOT NULL,
  FK_PROVIDER_ID              NOT NULL,
  REQUEST_DATA,               
  RESPONSE_DATA,              
  START_DATE,                 
  FINISH_DATE,                
  CREATED_BY                  NOT NULL,
  CREATED_DATE                NOT NULL,
  LAST_MODIFIED_BY            NOT NULL,
  LAST_MODIFIED_DATE          NOT NULL
)
TABLESPACE TESTD
PARTITION BY RANGE (CREATED_DATE)
SUBPARTITION BY HASH (PROVIDER_REQUEST_LOG_ID)
SUBPARTITION TEMPLATE
  (SUBPARTITION SP1 TABLESPACE TEST1D,
   SUBPARTITION SP2 TABLESPACE TEST1D,
   SUBPARTITION SP3 TABLESPACE TEST1D,
   SUBPARTITION SP4 TABLESPACE TEST1D
  ) 
(
PARTITION P_NOV16
VALUES LESS THAN (TO_DATE('01/12/2016','DD/MM/YYYY')) TABLESPACE TEST1D,
PARTITION P_DEC16 
VALUES LESS THAN(TO_DATE('01/01/2017','DD/MM/YYYY')) TABLESPACE TEST2D,
PARTITION P_JAN17
VALUES LESS THAN(TO_DATE('01/02/2017','DD/MM/YYYY')) TABLESPACE TEST3D,
PARTITION P_FEB17
VALUES LESS THAN (TO_DATE('01/03/2017','DD/MM/YYYY')) TABLESPACE TEST4D,
PARTITION P_MAR17 
VALUES LESS THAN(TO_DATE('01/04/2017','DD/MM/YYYY')) TABLESPACE TEST5D,
PARTITION P_APR17
VALUES LESS THAN(TO_DATE('01/05/2017','DD/MM/YYYY')) TABLESPACE TEST6D
)
enable row movement nologging 
as select /*+ PARALLEL(a,4)  */ a.* from TableA  a 
where trunc(A.CREATED_DATE)  > trunc(to_date('31/10/2016','DD/MM/YYYY')) ;

...

I tried to look at Exchange partition as well, but seems will not work for me as table has Range-Hash. I got ORA-14292.
I am looking for best and faster approach to do it.