Archiving Subpartition
To archive a subpartition, follow these steps:
1. Make the tablespace to be archived READ ONLY.
ALTER TABLESPACE CM_D1T304_P2011JAN_S181 READ ONLY;
2. Check the feasibility of archive using ILM_ARCH_SW = ‘N’.
Select count(1) from cisadm.D1_INIT_MSRMT_DATA SUBPARTITION P2011JAN_S181 where ILM_ARCH_SW = ‘N’;
IF the above query has a count of greater than ZERO records - Change the tablespace back to read and write mode. Archive cannot be done. Do not execute further steps. Stop archiving partition.
ALTER TABLESPACE CM_D1T304_P2011JAN_S181 READ WRITE;
IF the above query has ZERO records - Archive can be performed. Continue executing the remainder of the procedure.
3. Create separate archive tablespace for partition that needs to be archived.
CREATE BIGFILE TABLESPACE CM_D1T304_P2011JAN_S181_ARC DATAFILE '+DATA' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT ROW STORE COMPRESS ADVANCED;
4. Create staging tables and load data for all child tables for the MO first.
CREATE TABLE CM_D1T305_P2011JAN_S181_ARC PARALLEL NOLOGGING TABLESPACE CM_D1T304_P2011JAN_S181_ARC
AS
(
SELECT /*+ PARALLEL */ * FROM CISADM.D1_INIT_MSRMT_DATA_CHAR PARTITION (P2011JAN_S181)
);
CREATE TABLE CM_D1T306_P2011JAN_S181_ARC PARALLEL NOLOGGING TABLESPACE CM_D1T304_P2011JAN_S181_ARC
AS
(
SELECT /*+ PARALLEL */ * FROM CISADM.D1_INIT_MSRMT_DATA_LOG PARTITION (P2011JAN_S181)
);
CREATE TABLE CM_D1T307_P2011JAN_S181_ARC PARALLEL NOLOGGING TABLESPACE CM_D1T304_P2011JAN_S181_ARC
AS
(
SELECT /*+ PARALLEL */ * FROM CISADM.D1_INIT_MSRMT_DATA_LOG_PARM PARTITION (P2011JAN_S181)
);
ALTER TABLE CM_D1T305_P2011JAN_S181_ARC NOPARALLEL LOGGING;
ALTER TABLE CM_D1T306_P2011JAN_S181_ARC NOPARALLEL LOGGING;
ALTER TABLE CM_D1T307_P2011JAN_S181_ARC NOPARALLEL LOGGING;
5. Create staging table and load data for parent table
CREATE TABLE ALTER TABLE CM_D1T304_P2011JAN_S181_ARC NOPARALLEL LOGGING; NOLOGGING PARALLEL TABLESPACE CM_D1T304_P2011JAN_S181_ARC
AS
SELECT /*+ PARALLEL */ * FROM D1_INIT_MSRMT_DATA SUBPARTITION (P2011JAN_S181);
ALTER TABLE CM_D1T304_P2011JAN_S181_ARC NOPARALLEL LOGGING;
6. Export tablespace using TRANSPORT_TABLESPACES method.
ALTER TABLESPACE CM_D1T304_P2011JAN_S181_ARC READ ONLY;
expdp system/manager DIRECTORY=DUMP_DIR DUMPFILE=CM_D1T304_P2011JAN_S181_ARC.DMP TRANSPORT_TABLESPACES=CM_D1T304_P2011JAN_S181_ARC LOGFILE=EXP_CM_D1T304_P2011JAN_S181_ARC.LOG TRANSPORT_FULL_CHECK=Y
Make sure the tablespace datafile required for future import should be preserved.
<<Transport THE DATAFILE to the LOCAL DB DIRECTORY DUMP_DIR. For example if connected to asmcmd copy the file
cp cm_d1t304_p2011jan_tbs_ar.553.913864937 /tugbu_perf_02/BACKUPS/test_verification/ >>
7. Drop the partition, partition tablespace and archive tablespace (since they have been exported).
ALTER TABLE D1_INIT_MSRMT_DATA DROP SUBPARTITION P2011JAN_S181 UPDATE INDEXES;
DROP TABLESPACE CM_D1T304_P2011JAN_S181 INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE CM_D1T304_P2011JAN_S181_ARC INCLUDING CONTENTS AND DATAFILES;