Restore Partition
1. Create separate tablespace to restore the partition.
CREATE BIGFILE TABLESPACE CM_XT039_P2017JAN DATAFILE '+DATA' 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.CI_TD_ENTRY SPLIT PARTITION P2017FEB AT (TO_DATE('2017-02-01 00:00:01','SYYYY-MM-DD HH24:MI:SS'))
INTO
(
PARTITION P2017JAN TABLESPACE CM_XT039_P2017JAN , PARTITION P2017FEB
)
UPDATE INDEXES;
In case table contains LOBS like F1_SYNC_REQ_IN, there will be additional statement in split partition DDL indicating tablespace on which LOB should go.
ALTER TABLE CISADM.F1_SYNC_REQ_IN SPLIT PARTITION P2017FEB AT (TO_DATE('2017-02-01 00:00:01','SYYYY-MM-DD HH24:MI:SS'))
INTO
(
PARTITION P2017JAN TABLESPACE CM_F1T191_P2017JAN LOB(BO_DATA_AREA,PRE_TRN_INIT_BO_DATA_AREA,PRE_TRN_FIN_BO_DATA_AREA,POST_TRN_BO_DATA_AREA) STORE AS SECUREFILE (ENABLE STORAGE IN ROW COMPRESS MEDIUM CACHE TABLESPACE CM_F1T191_P2017JAN )
, PARTITION P2017FEB
)
UPDATE INDEXES;
3. Enable advanced compression after SPLIT partition as it will disable the compression.
ALTER TABLE CISADM.CI_TD_SRTKEY ROW STORE COMPRESS ADVANCED;
ALTER TABLE CISADM.CI_TD_MSG_PARM ROW STORE COMPRESS ADVANCED;
ALTER TABLE CISADM.CI_TD_DRLKEY ROW STORE COMPRESS ADVANCED;
ALTER TABLE CISADM.CI_TD_ENTRY_CHA ROW STORE COMPRESS ADVANCED;
ALTER TABLE CISADM.CI_TD_LOG ROW STORE COMPRESS ADVANCED;
4. Import tablespace using TRANSPORT_TABLESPACES method.
impdp system/manager DIRECTORY=DUMP_DIR DUMPFILE= CM_XT039_P2017JAN_ARC.DMP PARTITION_OPTIONS=DEPARTITION LOGFILE=IMP_CM_XT039_P2017JAN_ARC.LOG TRANSPORT_DATAFILES=/tugbu_perf_02/BACKUPS/test_verification/cm_xt039_p201701jan_ar.553.913864937
5. Load data into parent table first from the staging table
ALTER SESSION ENABLE PARALLEL DML;
INSERT /*+ APPEND PARALLEL */ INTO CISADM.CI_TD_ENTRY SELECT /*+ PARALLEL */ * FROM CM_XT039_P2017JAN_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 CISADM.CI_TD_ENTRY_CHA SELECT /*+ PARALLEL */ * FROM CM_XT701_P2017JAN_ARC;
COMMIT;
INSERT /*+ APPEND PARALLEL */ INTO CISADM.CI_TD_MSG_PARM SELECT /*+ PARALLEL */ * FROM CM_XT04_P2017JAN_ARC;
COMMIT;
INSERT /*+ APPEND PARALLEL */ INTO CISADM.CI_TD_LOG SELECT /*+ PARALLEL */ * FROM CM_XT721_P2017JAN_ARC;
COMMIT;
INSERT /*+ APPEND PARALLEL */ INTO CISADM.CI_TD_SRTKEY SELECT /*+ PARALLEL */ * FROM CM_XT041_P2017JAN_ARC;
COMMIT;
INSERT /*+ APPEND PARALLEL */ INTO CISADM.CI_TD_DRLKEY SELECT /*+ PARALLEL */ * FROM CM_XT037_P2017JAN_ARC;
COMMIT;
7. Drop the archive tablespace after import is import and data loading is successful.
DROP TABLESPACE CM_XT039_P2017JAN_ARC INCLUDING CONTENTS AND DATAFILES;