Thinking Out Loud

December 31, 2011

Happy New Year

Filed under: Uncategorized — mdinh @ 6:07 pm

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

December 14, 2011

datapump transform

Filed under: 11g,DataPump,oracle — mdinh @ 3:36 am

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

Blog at WordPress.com.