IMPDP: Another nice option TRANSFORM, explored

Consider this scenario (as explained by My colleague for his requirement, Thanks Rambo)

Source database: Tablespace T1 created with locally managed tablespace with auto segment space management.

Destination Database: Tablespace T1 created with locally managed tablespace with uniform extent allocation.

Read carefully the underline parts,

Now If I import this tablespace T1 into the Destination database what will happen

 Assuming those tables are not there in destination database

1) IMPDP first fire the create tablespace DDL statement as it like source database

For example:-

CREATE TABLE "HR"."EMPLOYEES"
   ( "EMPLOYEE_ID" NUMBER(6,0),
     "FIRST_NAME" VARCHAR2(20),
     "LAST_NAME" VARCHAR2(25) CONSTRAINT "EMP_LAST_NAME_NN" NOT NULL ENABLE,
     "EMAIL" VARCHAR2(25) CONSTRAINT "EMP_EMAIL_NN" NOT NULL ENABLE,
     "PHONE_NUMBER" VARCHAR2(20),
     "HIRE_DATE" DATE CONSTRAINT "EMP_HIRE_DATE_NN" NOT NULL ENABLE,
     "JOB_ID" VARCHAR2(10) CONSTRAINT "EMP_JOB_NN" NOT NULL ENABLE,
     "SALARY" NUMBER(8,2),
     "COMMISSION_PCT" NUMBER(2,2),
     "MANAGER_ID" NUMBER(6,0),
     "DEPARTMENT_ID" NUMBER(4,0)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 10240 NEXT 16384 MINEXTENTS 1 MAXEXTENTS 121
  PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SYSTEM" ;

Bold is the problematic part, why? read below the error

2) It will fail with following error, since the tablespace T1 in the destination is uniform extent allocation

ORA-39083: Object type TABLE:TEST"."PLAN_TABLE" failed to create with error:
ORA-02219: invalid NEXT storage option value

It is obvious that Oracle cannot translate the table level storage/segment parameters to uniform extents which like in destination, so it has to skip this part from above DDL command

  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 10240 NEXT 16384 MINEXTENTS 1 MAXEXTENTS 121
  PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

and it just should run the below while creating table during import by skipping the above

CREATE TABLE "HR"."EMPLOYEES"
   ( "EMPLOYEE_ID" NUMBER(6,0),
     "FIRST_NAME" VARCHAR2(20),
     "LAST_NAME" VARCHAR2(25) CONSTRAINT "EMP_LAST_NAME_NN" NOT NULL ENABLE,
     "EMAIL" VARCHAR2(25) CONSTRAINT "EMP_EMAIL_NN" NOT NULL ENABLE,
     "PHONE_NUMBER" VARCHAR2(20),
     "HIRE_DATE" DATE CONSTRAINT "EMP_HIRE_DATE_NN" NOT NULL ENABLE,
     "JOB_ID" VARCHAR2(10) CONSTRAINT "EMP_JOB_NN" NOT NULL ENABLE,
     "SALARY" NUMBER(8,2),
     "COMMISSION_PCT" NUMBER(2,2),
     "MANAGER_ID" NUMBER(6,0),
     "DEPARTMENT_ID" NUMBER(4,0)
   )   TABLESPACE "SYSTEM" ;

So now, nevertheless the tablespace is in locally/dictionary/ or uniform/auto allocate, this table will be created into that required tablespace in destination and inherits the tablespace level segment attributes as it does not contain the attributes any more.

To do, this Oracle IMPDP has a option called TRANSFORM which provides you to skip this parts

It contains the segment/storage/OID/PCTSPACE as TRANSFORM types

Syntax: transform = segment_attributes:n:table

Here at the place,

segment_attributes, you can define only storage,OID,PCTspace

and at table , you can define all objects (constraints,index,table which you want trim the segment attributes, but based on this table)

and at n, it can be y (default is y mean the impdp process by default ship the segment attributes in ddl)

So to perform above as per our requirement, our impdp syntax should like below.

impdp hr/hr TABLES=hr.employees DIRECTORY=dpump_dir1 DUMPFILE=hr_emp.dmp TRANSFORM=storage:n:table

then it will trim/skip the segment attributes part from the impdp dump and just run the create table statement as like above without the segment_attribute clauses

If you want just segment attributes (pct stuff) to be skipped, run

> impdp hr/hr TABLES=hr.employees DIRECTORY=dpump_dir1 DUMPFILE=hr_emp.dmp TRANSFORM=segment_attributes:n:table

If you want all segment attributes must be skipped while import, remove table option

> impdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=hr.dmp SCHEMAS=hr TRANSFORM=SEGMENT_ATTRIBUTES:n

If you want all storage attributes must be skipped while import, remove table option

> impdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=hr.dmp SCHEMAS=hr TRANSFORM=Storage:n

As, environments vary and the requirements demands are different, we should have this type of options available, looks nice feature to me.

Reference:- http://docs.oracle.com/cd/B19306_01/server.102/b14215/dp_import.htm

-Thanks

Geek DBA