TRANSFORM in datapump

TRANSFORM – In case you don’t want to import the objects storage attributes but just the content, you can use this parameter. TRANSFORM parameter instruct the data pump import job to modify the DDL that creates the object during import. You can modify 2 basic attributes SEGMENT_ATTRIBUTE and STORAGE.

SEGMENT_ATTRIBUTES - If the value is specified as y, then segment attributes (physical attributes, storage attributes, tablespaces, and logging) are included, with appropriate DDL. The default is y.

STORAGE - If the value is specified as y, the storage clauses are included, with appropriate DDL.

Example:

For the following example, assume that you have exported the employees table in the hr schema. The SQL CREATE TABLE statement that results when you then import the table is similar to the following:

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" ;

If you do not want to retain the STORAGE clause or TABLESPACE clause, you can remove them from the CREATE STATEMENT by using the Import TRANSFORM parameter. Specify the value of SEGMENT_ATTRIBUTES as n. This results in the exclusion of segment attributes (both storage and tablespace) from the table.

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

The resulting CREATE TABLE statement for the employees table would then look similar to the following

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)

   );

Enjoy:-)