...
$ impdp system/<password> dumpfile=sham_emp.dmp sqlfile=sham_emp.sql directory=dpdir ..
...
SQLFILE FOR SHAM_EMP.DMP
$ vi sham_emp.sql ..
...
-- new object type path: TABLE_EXPORT/TABLE/TABLE CREATE TABLE "SHAM"."EMP"
( "EMP_ID" NUMBER,
"EMP_NAME" VARCHAR2(30 BYTE), "GENDER" VARCHAR2(6 BYTE),
"DEPT_ID" NUMBER CONSTRAINT "EMP_DEPTID_C2_NTNL" NOT NULL ENABLE, "EMP_DESG" VARCHAR2(16 BYTE),
"ISACTIVE" VARCHAR2(6 BYTE), "EMP_HIRE_DATE" DATE,
"EMP_TERM_DATE" DATE,
"EMP_LEVEL" VARCHAR2(8 BYTE) ) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TBS1" ;
-- new object type path: TABLE_EXPORT/TABLE/INDEX/INDEX -- CONNECT SHAM
CREATE UNIQUE INDEX "SHAM"."EMP_EMPID_C1_PK" ON "SHAM"."EMP" ("EMP_ID") PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TBS1" PARALLEL 1 ;
ALTER INDEX "SHAM"."EMP_EMPID_C1_PK" NOPARALLEL;
CREATE INDEX "SHAM"."EMP_DPID_IN1" ON "SHAM"."EMP" ("DEPT_ID") PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" PARALLEL 1 ;
ALTER INDEX "SHAM"."EMP_DPID_IN1" NOPARALLEL;
-- new object type path: TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT -- CONNECT SYSTEM
ALTER TABLE "SHAM"."EMP" ADD CONSTRAINT "EMP_EMPID_C1_PK" PRIMARY KEY ("EMP_ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TBS1" ENABLE;
-- new object type path: TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS ..
...
If you do NOT want similar metadata, using TRANSFORM option you can change above definitions.
Let’s see some examples using TRANSFORM parameter.
EXAMPLE I - SEGMENT ATTRIBUTE = NO
$ impdp system/<password> dumpfile=sham_emp.dmp sqlfile=segmnt_attrbt.sql directory=dpdir transform=segment_attributes:n
..
...
SEGMENT_ATTRIBUTES:N option ignores all segment attributes (pink color) things for objects.
Now you can see NO (pink color) lines from the sqlfile. Now everything is default.
SQL FILE – SEGMNT_ATTRBT.SQL
$ vi segmnt_attrbt.sql ..
...
-- new object type path: TABLE_EXPORT/TABLE/TABLE
CREATE TABLE "SHAM"."EMP"
( "EMP_ID" NUMBER,
"EMP_NAME" VARCHAR2(30 BYTE), "GENDER" VARCHAR2(6 BYTE),
"DEPT_ID" NUMBER CONSTRAINT "EMP_DEPTID_C2_NTNL" NOT NULL ENABLE,
"EMP_DESG" VARCHAR2(16 BYTE), "ISACTIVE" VARCHAR2(6 BYTE), "EMP_HIRE_DATE" DATE,
"EMP_TERM_DATE" DATE,
"EMP_LEVEL" VARCHAR2(8 BYTE) ) ;
-- new object type path: TABLE_EXPORT/TABLE/INDEX/INDEX -- CONNECT SHAM
CREATE UNIQUE INDEX "SHAM"."EMP_EMPID_C1_PK" ON "SHAM"."EMP" ("EMP_ID");
ALTER INDEX "SHAM"."EMP_EMPID_C1_PK" NOPARALLEL;
CREATE INDEX "SHAM"."EMP_DPID_IN1" ON "SHAM"."EMP" ("DEPT_ID");
ALTER INDEX "SHAM"."EMP_DPID_IN1" NOPARALLEL;
-- new object type path: TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT -- CONNECT SYSTEM
ALTER TABLE "SHAM"."EMP" ADD CONSTRAINT "EMP_EMPID_C1_PK" PRIMARY KEY ("EMP_ID") ENABLE;
-- new object type path: TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS ..
...
EXAMPLE II - SEGMENT ATTRIBUTES FOR TABLE = NO
$ impdp system/<password> dumpfile=sham_emp.dmp directory=dpdir sqlfile=segmnt_attrbt_tab.sql transform=segment_attributes:n:table
..
...
SQL FILE - SEGMNT_ATTRBT_TAB.SQL
$ vi segmnt_attrbt_tab.sql ..
...
-- new object type path: TABLE_EXPORT/TABLE/TABLE
CREATE TABLE "SHAM"."EMP"
( "EMP_ID" NUMBER,
"EMP_NAME" VARCHAR2(30 BYTE), "GENDER" VARCHAR2(6 BYTE),
"DEPT_ID" NUMBER CONSTRAINT "EMP_DEPTID_C2_NTNL" NOT NULL ENABLE, "EMP_DESG" VARCHAR2(16 BYTE),
"ISACTIVE" VARCHAR2(6 BYTE), "EMP_HIRE_DATE" DATE,
"EMP_TERM_DATE" DATE,
"EMP_LEVEL" VARCHAR2(8 BYTE) ) ;
-- new object type path: TABLE_EXPORT/TABLE/INDEX/INDEX -- CONNECT SHAM
CREATE UNIQUE INDEX "SHAM"."EMP_EMPID_C1_PK" ON "SHAM"."EMP" ("EMP_ID") PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TBS1" PARALLEL 1 ;
ALTER INDEX "SHAM"."EMP_EMPID_C1_PK" NOPARALLEL;
CREATE INDEX "SHAM"."EMP_DPID_IN1" ON "SHAM"."EMP" ("DEPT_ID") PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" PARALLEL 1 ;
ALTER INDEX "SHAM"."EMP_DPID_IN1" NOPARALLEL;
-- new object type path: TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT -- CONNECT SYSTEM
ALTER TABLE "SHAM"."EMP" ADD CONSTRAINT "EMP_EMPID_C1_PK" PRIMARY KEY ("EMP_ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TBS1" ENABLE;
-- new object type path: TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS ..
...
Now attributes are ignored for table only.
EXAMPLE III - SEGMENT ATTRIBUTES FOR INDEX=NO
$ impdp system/<password> dumpfile=sham_emp.dmp sqlfile=segmnt_attrbt_ind.sql directory=dpdir transform=segment_attributes:n:index
..
...
SQLFILE - SEGMNT_ATTRBT_IND.SQL
Segmnt_attrbt_ind.sql
Now attributes are ignored for indexes only.
EXAMPLE IV - STORAGE =NO
By default objects will get
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
$ impdp system/<password> dumpfile=sham_emp.dmp directory=dpdir sqlfile=storage.sql transform=storage:n
SQLFILE – STORAGE.SQL
$ vi storage.sql ..
...
-- new object type path: TABLE_EXPORT/TABLE/TABLE CREATE TABLE "SHAM"."EMP"
( "EMP_ID" NUMBER,
"EMP_NAME" VARCHAR2(30 BYTE), "GENDER" VARCHAR2(6 BYTE),
"DEPT_ID" NUMBER CONSTRAINT "EMP_DEPTID_C2_NTNL" NOT NULL ENABLE, "EMP_DESG" VARCHAR2(16 BYTE),
"ISACTIVE" VARCHAR2(6 BYTE), "EMP_HIRE_DATE" DATE,
"EMP_TERM_DATE" DATE,
"EMP_LEVEL" VARCHAR2(8 BYTE) ) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING TABLESPACE "TBS1" ;
-- new object type path: TABLE_EXPORT/TABLE/INDEX/INDEX -- CONNECT SHAM
CREATE UNIQUE INDEX "SHAM"."EMP_EMPID_C1_PK" ON "SHAM"."EMP" ("EMP_ID") PCTFREE 10 INITRANS 2 MAXTRANS 255
TABLESPACE "TBS1" PARALLEL 1 ;
ALTER INDEX "SHAM"."EMP_EMPID_C1_PK" NOPARALLEL;
CREATE INDEX "SHAM"."EMP_DPID_IN1" ON "SHAM"."EMP" ("DEPT_ID") PCTFREE 10 INITRANS 2 MAXTRANS 255
TABLESPACE "USERS" PARALLEL 1 ;
ALTER INDEX "SHAM"."EMP_DPID_IN1" NOPARALLEL;
-- new object type path: TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT -- CONNECT SYSTEM
ALTER TABLE "SHAM"."EMP" ADD CONSTRAINT "EMP_EMPID_C1_PK" PRIMARY KEY ("EMP_ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
TABLESPACE "TBS1" ENABLE;
-- new object type path: TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS ..
Now Storage clause is ignored.
EXAMPLE V – CHANGE THE PCTSPACE
PCTSPACE parameter helpful to either reduce/increase the storage space. Whatever value you put (interms of percentage), based on metadata value its resize.
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TBS1" ;
Now I specify pctspace=10, it will initially allocate 20% extents and also for next extent, while importing the data.
$ impdp system/manager dumpfile=sham_emp.dmp directory=dp sqlfile=emp_pct.sql transform=pctspace:10
..
...
SQLFILE – EMP_PCT.SQL
$ vi emp_pct.sql ..
...
-- new object type path: TABLE_EXPORT/TABLE/TABLE CREATE TABLE "SHAM"."EMP"
( "EMP_ID" N..UMBER,
"EMP_NAME" VARCHAR2(30 BYTE), "GENDER" VARCHAR2(6 BYTE),
"DEPT_ID" NUMBER CONSTRAINT "EMP_DEPTID_C2_NTNL" NOT NULL ENABLE, "EMP_DESG" VARCHAR2(16 BYTE),
"ISACTIVE" VARCHAR2(6 BYTE), "EMP_HIRE_DATE" DATE,
"EMP_TERM_DATE" DATE,
"EMP_LEVEL" VARCHAR2(8 BYTE) ) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING