Subscribe to Posts by Email

Subscriber Count

    696

Disclaimer

All information is offered in good faith and in the hope that it may be of use for educational purpose and for Database community purpose, but is not guaranteed to be correct, up to date or suitable for any particular purpose. db.geeksinsight.com accepts no liability in respect of this information or its use. This site is independent of and does not represent Oracle Corporation in any way. Oracle does not officially sponsor, approve, or endorse this site or its content and if notify any such I am happy to remove. Product and company names mentioned in this website may be the trademarks of their respective owners and published here for informational purpose only. This is my personal blog. The views expressed on these pages are mine and learnt from other blogs and bloggers and to enhance and support the DBA community and this web blog does not represent the thoughts, intentions, plans or strategies of my current employer nor the Oracle and its affiliates or any other companies. And this website does not offer or take profit for providing these content and this is purely non-profit and for educational purpose only. If you see any issues with Content and copy write issues, I am happy to remove if you notify me. Contact Geek DBA Team, via geeksinsights@gmail.com

Pages

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

2 comments to IMPDP: Another nice option TRANSFORM, explored

  • chintan

    Hello Geek DBA,
    i want to learn import/export & try some hans on issue so plz u have any document or book,example plz give me so i can very well in import/export.

    • Hello Chintan,

      I would suggest explore options help=y with impdp/expdp or documentation, there is no specific book I can recommend only for this.

      -THnaks
      Geek DBA