Archive Partition
1. Make the tablespace to be archived READ ONLY.
ALTER TABLESPACE CM_XT039_P2017JAN READ ONLY;
2. Check the feasibility of archive using ILM_ARCH_SW = ‘N’.
Select count(1) from CISADM.CI_TD_ENTRY PARTITION P2017JAN where ILM_ARCH_SW = ‘N’;
• If Yes (count of records of above query is ZERO), then proceed for further steps.
• If No (count of records of above query is Non ZERO), then make the tablespace back to READ WRITE MODE as Archive is not Feasible at the time.
ALTER TABLESPACE CM_XT039_P2017JAN READ WRITE;
3. Create separate archive tablespace for partition need to be archived.
CREATE BIGFILE TABLESPACE CM_XT039_P2017JAN_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.
a. CI_TD_ENTRY_CHA
CREATE TABLE CM_XT701_P2017JAN_ARC PARALLEL NOLOGGING TABLESPACE CM_XT039_P2017JAN_ARC
AS
(
SELECT /*+ PARALLEL */ * FROM CISADM.CI_TD_ENTRY_CHA PARTITION (P2017JAN_S01)
UNION ALL
SELECT /*+ PARALLEL */ * FROM CI_TD_ENTRY_CHA PARTITION (P2017JAN_S02)
UNION ALL
.
.
.
.
UNION ALL
SELECT /*+ PARALLEL */ * FROM CI_TD_ENTRY_CHA PARTITION (P2017JAN_S08)
);
ALTER TABLE CM_XT701_P2017JAN_ARC NOPARALLEL LOGGING;
b. CI_TD_MSG_PARM
CREATE TABLE CM_XT04_P2017JAN_ARC PARALLEL NOLOGGING TABLESPACE CM_XT039_P2017JAN_ARC
AS
(
SELECT /*+ PARALLEL */ * FROM CISADM.CI_TD_MSG_PARM PARTITION (P2017JAN_S01)
UNION ALL
SELECT /*+ PARALLEL */ * FROM CI_TD_MSG_PARM PARTITION (P2017JAN_S02)
UNION ALL
.
.
.
.
UNION ALL
SELECT /*+ PARALLEL */ * FROM CI_TD_MSG_PARM PARTITION (P2017JAN_S08)
);
ALTER TABLE CM_XT04_P2017JAN_ARC NOPARALLEL LOGGING;
c. CI_TD_LOG
CREATE TABLE CM_XT721_P2017JAN_ARC PARALLEL NOLOGGING TABLESPACE CM_XT039_P2017JAN_ARC
AS
(
SELECT /*+ PARALLEL */ * FROM CISADM.CI_TD_LOG PARTITION (P2017JAN_S01)
UNION ALL
SELECT /*+ PARALLEL */ * FROM CI_TD_LOG PARTITION (P2017JAN_S02)
UNION ALL
.
.
.
.
UNION ALL
SELECT /*+ PARALLEL */ * FROM CI_TD_LOG PARTITION (P2017JAN_S08)
);
ALTER TABLE CM_XT721_P2017JAN_ARC NOPARALLEL LOGGING;
d. CI_TD_SRTKEY
CREATE TABLE CM_XT041_P2017JAN_ARC PARALLEL NOLOGGING TABLESPACE CM_XT039_P2017JAN_ARC
AS
(
SELECT /*+ PARALLEL */ * FROM CISADM.CI_TD_SRTKEY PARTITION (P2017JAN_S01)
UNION ALL
SELECT /*+ PARALLEL */ * FROM CI_TD_SRTKEY PARTITION (P2017JAN_S02)
UNION ALL
.
.
.
.
UNION ALL
SELECT /*+ PARALLEL */ * FROM CI_TD_SRTKEY PARTITION (P2017JAN_S08)
);
ALTER TABLE CM_XT041_P2017JAN_ARC NOPARALLEL LOGGING;
e. CI_TD_DRLKEY
CREATE TABLE CM_XT037_P2017JAN_ARC PARALLEL NOLOGGING TABLESPACE CM_XT039_P2017JAN_ARC
AS
(
SELECT /*+ PARALLEL */ * FROM CISADM.CI_TD_DRLKEY PARTITION (P2017JAN_S01)
UNION ALL
SELECT /*+ PARALLEL */ * FROM CISADM.CI_TD_DRLKEY PARTITION (P2017JAN_S02)
UNION ALL
.
.
.
.
UNION ALL
SELECT /*+ PARALLEL */ * FROM CISADM.CI_TD_DRLKEY PARTITION (P2017JAN_S08)
);
ALTER TABLE CM_XT037_P2017JAN_ARC NOPARALLEL LOGGING;
5. Create staging table and load data for parent table.
CREATE TABLE CM_XT039_P2017JAN_ARC NOLOGGING PARALLEL TABLESPACE CM_XT039_P2017JAN_ARC AS
SELECT /*+ PARALLEL */ * FROM CISADM.CI_TD_ENTRY PARTITION (P2017JAN);
ALTER TABLE CM_XT039_P2017JAN_ARC NOPARALLEL LOGGING;
6. Export tablespace using TRANSPORT_TABLESPACES method.
ALTER TABLESPACE CM_XT039_P2017JAN_ARC READ ONLY;
expdp system/manager DIRECTORY=DUMP_DIR DUMPFILE= CM_XT039_P2017JAN_ARC.DMP TRANSPORT_TABLESPACES = CM_XT039_P2017JAN_ARC LOGFILE=EXP_ CM_XT039_P2017JAN_ARC.LOG TRANSPORT_FULL_CHECK=Y
Ensure tablespace datafile required for further import should be preserved.
<<Transport THE FILE to LOCAL DB DIRECTORY DUMP_DIR like connected to asmcmd and copied the file from cp cm_xt039_p201701_tbs_ar.553.913864937 /tugbu_perf_02/BACKUPS/test_verification/ >>
7. Drop the partition, partition tablespace and archive tablespace (as it is already exported).
ALTER TABLE CISADM.CI_TD_ENTRY DROP PARTITION P2017JAN UPDATE INDEXES;
DROP TABLESPACE CM_XT039_P2017JAN INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE CM_XT039_P2017JAN_ARC INCLUDING CONTENTS AND DATAFILES;