Happy New Year

An amazing photo at 500px: http://500px.com/photo/4039804

Advertisements

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