An amazing photo at 500px: http://500px.com/photo/4039804
December 31, 2011
December 14, 2011
datapump transform
I know there are many blogs out there on datapump; however, I wanted to just to create a quick post on parallel datapump and transform.
The requirement is to parallel export and import and to migrate indexes to a new tablespace as part of a DW migration.
Export parameter:
DIRECTORY=data_pump_local DUMPFILE=mdinh%U.dmp SCHEMAS=mdinh PARALLEL=12 METRICS=y LOGFILE=expdp_mdinh.log EXCLUDE=grant,statistics REUSE_DUMPFILES=Y FLASHBACK_SCN=10535801779818
Import parameter (NO TRANSFORM):
DIRECTORY=data_pump_local DUMPFILE=mdinh%U.dmp SCHEMAS=mdinh PARALLEL=12 METRICS=y LOGFILE=impdp_mdinh.log INCLUDE=constraint,index SQLFILE=mdinh_notransform.sql
> cat mdinh_notransform.sql
-- CONNECT OPS$ORACLE ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1'; ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1'; ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1'; ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1'; ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1'; ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 '; -- new object type path: SCHEMA_EXPORT/TABLE/INDEX/INDEX -- CONNECT MDINH CREATE UNIQUE INDEX "MDINH"."PKP" ON "MDINH"."P" ("ID") PCTFREE 10 INITRANS 2 MAXTRANS 255 TABLESPACE "USER_DATA" PARALLEL 1 ; ALTER INDEX "MDINH"."PKP" NOPARALLEL; CREATE INDEX "MDINH"."X" ON "MDINH"."F" ("ID") PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE( INITIAL 1048576) TABLESPACE "USER_DATA" PARALLEL 1 ; ALTER INDEX "MDINH"."X" NOPARALLEL; -- new object type path: SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT -- CONNECT OPS$ORACLE ALTER TABLE "MDINH"."P" ADD CONSTRAINT "PKP" PRIMARY KEY ("ID") RELY USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 TABLESPACE "USER_DATA" ENABLE; -- new object type path: SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT ALTER TABLE "MDINH"."F" ADD CONSTRAINT "F1" FOREIGN KEY ("ID") REFERENCES "MDINH"."P" ("ID") RELY DISABLE;
Import parameter (TRANSFORM):
DIRECTORY=data_pump_local DUMPFILE=mdinh%U.dmp SCHEMAS=mdinh PARALLEL=12 METRICS=y LOGFILE=impdp_mdinh.log INCLUDE=constraint,index SQLFILE=mdinh_transform.sql TRANSFORM=STORAGE:n:index TRANSFORM=STORAGE:n:constraint REMAP_TABLESPACE=user_data:user_index
> cat mdinh_transform.sql
-- CONNECT OPS$ORACLE ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1'; ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1'; ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1'; ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1'; ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1'; ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 '; -- new object type path: SCHEMA_EXPORT/TABLE/INDEX/INDEX -- CONNECT MDINH CREATE UNIQUE INDEX "MDINH"."PKP" ON "MDINH"."P" ("ID") PCTFREE 10 INITRANS 2 MAXTRANS 255 TABLESPACE "USER_INDEX" PARALLEL 1 ; ALTER INDEX "MDINH"."PKP" NOPARALLEL; CREATE INDEX "MDINH"."X" ON "MDINH"."F" ("ID") PCTFREE 10 INITRANS 2 MAXTRANS 255 TABLESPACE "USER_INDEX" PARALLEL 1 ; ALTER INDEX "MDINH"."X" NOPARALLEL; -- new object type path: SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT -- CONNECT OPS$ORACLE ALTER TABLE "MDINH"."P" ADD CONSTRAINT "PKP" PRIMARY KEY ("ID") RELY USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 TABLESPACE "USER_INDEX" ENABLE; -- new object type path: SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT ALTER TABLE "MDINH"."F" ADD CONSTRAINT "F1" FOREIGN KEY ("ID") REFERENCES "MDINH"."P" ("ID") RELY DISABLE;
Did you notice the storage clause in the mdinh_notransform.sql?
REFERENCE: Data Pump Import: TRANSFORM