Restoring Subpartition

To restore the subpartition, follow these steps:

1. Create separate tablespace to restore the partition.

CREATE BIGFILE TABLESPACE CM_D1T304_P2011JAN_S181 DATAFILE 'DATADG' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT ROW STORE COMPRESS ADVANCED;

2. Add partition using split operation on next greater value partition.

ALTER TABLE CISADM.D1_INIT_MSRMT_DATA SPLIT SUBPARTITION P2011JAN_SMAX AT (181)

INTO

(

SUBPARTITION P2011JAN_S181 TABLESPACE CM_D1T304_P2011JAN_S181 LOB(IMD_BO_DATA_AREA, PREVEE_BO_DATA_AREA, POSTVEE_BO_DATA_AREA, TRACE_BO_DATA_AREA, RAW_BO_DATA_AREA) STORE AS SECUREFILE (ENABLE STORAGE IN ROW COMPRESS MEDIUM CACHE TABLESPACE CM_D1T304_P2011JAN_S181)

, SUBPARTITION P2011JAN_SMAX) UPDATE INDEXES;

3. Enable advanced compression after SPLIT partition as it will disable the compression.

ALTER TABLE D1_INIT_MSRMT_DATA_CHAR ROW STORE COMPRESS ADVANCED;

ALTER TABLE D1_INIT_MSRMT_DATA_LOG ROW STORE COMPRESS ADVANCED;

ALTER TABLE D1_INIT_MSRMT_DATA_LOG_PARM ROW STORE COMPRESS ADVANCED;

4. Import tablespace using TRANSPORT_TABLESPACES method.

impdp system/manager DIRECTORY=DUMP_DIR DUMPFILE=CM_D1T304_P2011JAN_S181_ARC.DMP PARTITION_OPTIONS=DEPARTITION LOGFILE=IMP_CM_D1T304_P2011JAN_S181_ARC.LOG TRANSPORT_DATAFILES=/tugbu_perf_02/BACKUPS/test_verification/cm_d1t304_p2011jan_tbs_ar.553.913864937

5. Load data into parent table first from the staging table.

ALTER SESSION ENABLE PARALLEL DML;

INSERT /*+ APPEND PARALLEL */ INTO CISADM.D1_INIT_MSRMT_DATA SELECT /*+ PARALLEL */ * FROM CM_D1T304_P2011JAN_S181_ARC;

COMMIT;

6. Load data into child table from the staging table.

For each Child IN LIST OF CHILD TABLES, perform the following:

INSERT /*+ APPEND PARALLEL */ INTO D1_INIT_MSRMT_DATA_CHAR SELECT /*+ PARALLEL */ * FROM CM_D1T305_P2011JAN_S181_ARC;

COMMIT;

INSERT /*+ APPEND PARALLEL */ INTO D1_INIT_MSRMT_DATA_LOG SELECT /*+ PARALLEL */ * FROM CM_D1T306_P2011JAN_S181_ARC;

COMMIT;

INSERT /*+ APPEND PARALLEL */ INTO D1_INIT_MSRMT_DATA_LOG_PARM SELECT /*+ PARALLEL */ * FROM CM_D1T307_P2011JAN_S181_ARC;

COMMIT;

7. Drop the archive tablespace after import is import and data loading is successful.

DROP TABLESPACE CM_D1T304_P2011JAN_S181_ARC INCLUDING CONTENTS AND DATAFILES;