Data Pump Impdp TRANSFORM option SEGMENT_ATTRIBUTES, STORAGE, OID and PCTSPACE examples


Data pump impdp has many options to transform the metadata during the import operation. Those are REMAP_DATAFILE, REMAP_SCHEMA, REMAP_TABLESPACE and the TRANSFORM.
Using this option you can alter specific objects ddls and applicable for all the objects being imported.
Usage: TRANSFORM = transform_name:value[:object_type]
These are the applicable transform_names
  1. SEGMENT_ATTRIBUTES: by default value is y which will copy the objects as it is in the export dump with all segment attributes. If you specify the value as n the import job will omit the segment_attributes in the dump file and it will use the tablespace/user default values.
  2. STORAGE: by default the value for this parameter is y which will include all storage clauses during the import job. If you specify the parameter value as n then it will omit the storage clause in the dump file and it will follow the default values in the tablespace.
  3. PCTSPACE: it is the percent multiplier for the extent allocations and size of the datafiles during the import.
  4. OID: object id (OID) mainly used for the TYPE objects. Each and every type is identified by OID which will be unique. If you create a type without specifying the OID the RDBMS itself will create and assign unique OID to the new TYPE object. See below examples for more details.
If you specify transform=OID:y:type the import job will import the type with same OID during the export, otherwise it will assign a new OID during the import. Example is mentioned below.

Example on SEGMENT_ATTRIBUTES & STORAGE
I have table called emp in my schema and tablespace as siebel. I am going to change its default storage parameters, tablespace etc.

SQL> ALTER TABLE SCOTT.EMP STORAGE(  NEXT 50M);

Table altered.


SQL> ALTER TABLE SCOTT.EMP PCTFREE 20;

Table altered.

I am changing the default tablespace for the user from Siebel to users.
SQL> ALTER USER SCOTT DEFAULT TABLESPACE USERS;

User altered.

Now the table definition is as follows
SQL> select dbms_metadata.get_ddl('TABLE','EMP','SCOTT') from dual;

DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT')
---------------------------------------------------------

  CREATE TABLE "SCOTT"."EMP"
   (    "EMPNO" NUMBER(4,0) NOT NULL ENABLE,
        "ENAME" VARCHAR2(10),
        "JOB" VARCHAR2(9),
        "MGR" NUMBER(4,0),
        "HIREDATE" DATE,
        "SAL" NUMBER(7,2),
        "COMM" NUMBER(7,2),
        "DEPTNO" NUMBER(2,0)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 20 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 52428800 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SIEBEL"

Taking the export of the emp table.
 expdp job_name=schemaexp1 tables=scott.emp dumpfile=exp_emp.dmp logfile=exp_emp.log directory=EXP_DIR compression=all

Export: Release 11.2.0.2.0 - Production on Wed Nov 28 06:19:05 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Starting "SYS"."SCHEMAEXP1":  /******** AS SYSDBA job_name=schemaexp1 tables=scott.emp dumpfile=exp_emp.dmp logfile=exp_emp.log directory=EXP_DIR compression=all
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."EMP"                             5.617 KB      14 rows
Master table "SYS"."SCHEMAEXP1" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SCHEMAEXP1 is:
  /u01/backup1/export/exp_emp.dmp
Job "SYS"."SCHEMAEXP1" successfully completed at 06:19:16

Dropping the table from the schema.
SQL> drop table scott.emp;

Table dropped.

Importing the table with SEGMENT_ATTRIBUTES:n:table,STORAGE:N:table which will create the table with user default values. See the values marked in RED color after import.

$ impdp dumpfile=exp_emp.dmp logfile=imp_emp.log directory=EXP_DIR full=y transform=SEGMENT_ATTRIBUTES:n:table,STORAGE:N:table

Import: Release 11.2.0.2.0 - Production on Wed Nov 28 06:24:05 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Username: / AS SYSDBA

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  /******** AS SYSDBA dumpfile=exp_emp.dmp logfile=imp_emp.log directory=EXP_DIR full=y transform=SEGMENT_ATTRIBUTES:n:table,STORAGE:N:table
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."EMP"                             5.617 KB      14 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at 06:24:10


SQL> select dbms_metadata.get_ddl('TABLE','EMP','SCOTT') from dual;

DBMS_METADATA.GET_DDL('TABLE','EMP','SCOTT')
---------------------------------------------------------

  CREATE TABLE "SCOTT"."EMP"
   (    "EMPNO" NUMBER(4,0) NOT NULL ENABLE,
        "ENAME" VARCHAR2(10),
        "JOB" VARCHAR2(9),
        "MGR" NUMBER(4,0),
        "HIREDATE" DATE,
        "SAL" NUMBER(7,2),
        "COMM" NUMBER(7,2),
        "DEPTNO" NUMBER(2,0)
   ) 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 DE
FAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"


Example on OID
Create a type with unique OID.
SQL> SELECT SYS_OP_GUID() FROM DUAL;


SYS_OP_GUID()
--------------------------------
CF8C98BA272CA45CE0400C0A8D6776F1

CREATE TYPE APPtype OID 'TESTOID'
AS OBJECT (attrib1 NUMBER);

SQL> CREATE TYPE APPtype OID 'CF8C98BA272CA45CE0400C0A8D6776F1'
     AS OBJECT (attrib1 NUMBER);
     /

Type created.

SQL> select TYPE_NAME, TYPE_OID from user_types where TYPE_NAME='APPTYPE';

TYPE_NAME                      TYPE_OID
------------------------------ --------------------------------
APPTYPE                        CF8C98BA272CA45CE0400C0A8D6776F1

Taking the export of only TYPE objects from the schema using include option

expdp job_name=schemaexp1 schemas=scott include=type dumpfile=exp_type.dmp logfile=exp_type.log directory=EXP_DIR compression=all

Export: Release 11.2.0.2.0 - Production on Wed Nov 28 05:31:07 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Starting "SYS"."SCHEMAEXP1":  /******** AS SYSDBA job_name=schemaexp1 schemas=scott include=type dumpfile=exp_type.dmp logfile=exp_type.log directory=EXP_DIR compression=all
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Master table "SYS"."SCHEMAEXP1" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SCHEMAEXP1 is:
  /u01/backup1/export/exp_type.dmp
Job "SYS"."SCHEMAEXP1" successfully completed at 05:31:17

Dropping the type
SQL> drop type APPTYPE;

Type dropped.

Importing the type object with transform=OID:n:type which will create the type object with new OID value.
$ impdp dumpfile=exp_type.dmp logfile=imp_exp_type.log directory=EXP_DIR full=y transform=OID:n:type

Import: Release 11.2.0.2.0 - Production on Wed Nov 28 05:34:40 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  /******** AS SYSDBA dumpfile=exp_type.dmp logfile=imp_exp_type.log directory=EXP_DIR full=y transform=OID:n:type
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at 05:34:45

Object created with new OID value.
SQL> select TYPE_NAME, TYPE_OID from user_types where TYPE_NAME='APPTYPE';


TYPE_NAME                      TYPE_OID
------------------------------ --------------------------------
APPTYPE                        CF8CB70D758454DAE0400C0A8D671725

SQL> drop type APPTYPE;

Type dropped.
Importing the dump with transform=OID:y:type which will maintain the same OID values during the export.

$ impdp dumpfile=exp_type.dmp logfile=imp_exp_type.log directory=EXP_DIR full=y transform=OID:y:type

Import: Release 11.2.0.2.0 - Production on Wed Nov 28 05:35:47 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  /******** AS SYSDBA dumpfile=exp_type.dmp logfile=imp_exp_type.log directory=EXP_DIR full=y transform=OID:y:type
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at 05:35:56

Type object imported with same OID during the export.
SQL> select TYPE_NAME, TYPE_OID from user_types where TYPE_NAME='APPTYPE';

TYPE_NAME                      TYPE_OID
------------------------------ --------------------------------
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google