Tuesday, September 24, 2013

TRANSFORM in ORACLE 11g DATAPUMP

                                  TRANSFORM in ORACLE 11g DATAPUMP

Purpose
Enables you to alter object creation DDL for objects being imported.
Syntax and Description
TRANSFORM = transform_name:value[:object_type]
The transform_name specifies the name of the transform. The possible options are as follows:
  • 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, then the storage clauses are included, with appropriate DDL. The default is y. This parameter is ignored if SEGMENT_ATTRIBUTES=n.
  • OID - If the value is specified as n, then the assignment of the exported OID during the creation of object tables and types is inhibited. Instead, a new OID is assigned. This can be useful for cloning schemas, but does not affect referenced objects. The default value is y.
  • PCTSPACE - The value supplied for this transform must be a number greater than zero. It represents the percentage multiplier used to alter extent allocations and the size of data files.
Note that you can use the PCTSPACE transform with the Data Pump Export SAMPLE parameter so that the size of storage allocations matches the sampled data subset.
  • SEGMENT_CREATION - If set to y (the default), then this transform causes the SQL SEGMENT CREATION clause to be added to the CREATE TABLE statement. That is, the CREATE TABLE statement will explicitly say either SEGMENT CREATION DEFERRED or SEGMENT CREATION IMMEDIATE. If the value is n, then the SEGMENT CREATION clause is omitted from the CREATE TABLE statement. Set this parameter to n to use the default segment creation attributes for the table(s) being loaded. (This functionality is available starting with Oracle Database 11g release 2 (11.2.0.2).)
The type of value specified depends on the transform used. Boolean values (y/n) are required for the SEGMENT_ATTRIBUTES, STORAGE, and OID transforms. Integer values are required for the PCTSPACE transform.

The object_type is optional. If supplied, it designates the object type to which the transform will be applied. If no object type is specified, then the transform applies to all valid object types.

Valid Object Types For the Data Pump Import TRANSFORM Parameter
SEGMENT_ATTRIBUTES
STORAGE
OID
PCTSPACE
SEGMENT_CREATION
CLUSTER
X
X

X

CONSTRAINT
X
X

X

INC_TYPE


X


INDEX
X
X

X

ROLLBACK_SEGMENT
X
X

X

TABLE
X
X
X
X
X
TABLESPACE
X


X

TYPE


X




First lets assume we took the export  full or schema mode export.
$expdp full=y dumpfile=d1.dmp directory=dps

Case 1:  Normal import 

[oracle@racs dp]$ impdp tables=scott.emp dumpfile=d1.dmp directory=dps sqlfile=emp.sql

Import: Release 11.2.0.3.0 - Production on Tue Sep 24 09:49:19 2013

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

Username: dpusr
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "DPUSR"."SYS_SQL_FILE_TABLE_01" successfully loaded/unloaded
Starting "DPUSR"."SYS_SQL_FILE_TABLE_01":  dpusr/******** tables=scott.emp dumpfile=d1.dmp directory=dps sqlfile=emp.sql
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
.......
Job "DPUSR"."SYS_SQL_FILE_TABLE_01" successfully completed at 09:49:31

[oracle@racs dp]$ vi emp.sql


CREATE TABLE "SCOTT"."EMP"
   (    "EMPNO" NUMBER(4,0),
        "ENAME" VARCHAR2(10 BYTE),
        "JOB" VARCHAR2(9 BYTE),
        "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 DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;
-- new object type path: DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
-- CONNECT SCOTT
CREATE UNIQUE INDEX "SCOTT"."PK_EMP" ON "SCOTT"."EMP" ("EMPNO")
  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 "SCOTT"."PK_EMP" NOPARALLEL;
-- new object type path: DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
-- CONNECT DPUSR
ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
  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 "USERS"  ENABLE;

Using TRANSFORM option you can change or ignore these options.  Benefit is you may do not want similar metadata.  Forexample, tablespace name is pointing as USERS, current import user may be using different tablespace. As you know whenever we create a table , if your not specified the tablespace, it will take default user's tablespace.  If you want go like that case means you need to use TRANSFORM options. etc.

Case 2:  SEGMENT ATTRIBUTE option with N

 It will ignore all segment attributes(pink color) things for objects(you can verify the table for applicable objects). 

 [oracle@racs dp]$ impdp tables=scott.emp dumpfile=d1.dmp directory=dps sqlfile=emp_seg.sql transform=segment_attributes:n

Import: Release 11.2.0.3.0 - Production on Tue Sep 24 09:51:17 2013

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

Username: dpusr
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "DPUSR"."SYS_SQL_FILE_TABLE_01" successfully loaded/unloaded
Starting "DPUSR"."SYS_SQL_FILE_TABLE_01":  dpusr/******** tables=scott.emp dumpfile=d1.dmp directory=dps sqlfile=emp_seg.sql transform=segment_attributes:n
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
....
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
Job "DPUSR"."SYS_SQL_FILE_TABLE_01" successfully completed at 09:51:30


[oracle@racs dp]$ vi emp_seg.sql
  CREATE TABLE "SCOTT"."EMP"
   (    "EMPNO" NUMBER(4,0),
        "ENAME" VARCHAR2(10 BYTE),
        "JOB" VARCHAR2(9 BYTE),
        "MGR" NUMBER(4,0),
        "HIREDATE" DATE,
        "SAL" NUMBER(7,2),
        "COMM" NUMBER(7,2),
        "DEPTNO" NUMBER(2,0)
   ) ;
-- new object type path: DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
-- CONNECT SCOTT
CREATE UNIQUE INDEX "SCOTT"."PK_EMP" ON "SCOTT"."EMP" ("EMPNO")
  ;

  ALTER INDEX "SCOTT"."PK_EMP" NOPARALLEL;
-- new object type path: DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
-- CONNECT DPUSR
ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO") ENABLE;
-- new object type path: DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Now everything is default.   

Case 3: ignore SEGMENT ATTRIBUTES FOR TABLE

[oracle@racs dp]$ impdp tables=scott.emp dumpfile=d1.dmp directory=dps sqlfile=emp_seg_tab.sql transform=segment_attributes:n:table

Import: Release 11.2.0.3.0 - Production on Tue Sep 24 09:52:47 2013

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

Username: dpusr
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "DPUSR"."SYS_SQL_FILE_TABLE_01" successfully loaded/unloaded
Starting "DPUSR"."SYS_SQL_FILE_TABLE_01":  dpusr/******** tables=scott.emp dumpfile=d1.dmp directory=dps sqlfile=emp_seg_tab.sql transform=segment_attributes:n:table
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
....
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
Job "DPUSR"."SYS_SQL_FILE_TABLE_01" successfully completed at 09:52:59

[oracle@racs dp]$ vi emp_seg_tab.sql


CREATE TABLE "SCOTT"."EMP"
   (    "EMPNO" NUMBER(4,0),
        "ENAME" VARCHAR2(10 BYTE),
        "JOB" VARCHAR2(9 BYTE),
        "MGR" NUMBER(4,0),
        "HIREDATE" DATE,
        "SAL" NUMBER(7,2),
        "COMM" NUMBER(7,2),
        "DEPTNO" NUMBER(2,0)
   ) ;
-- new object type path: DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
-- CONNECT SCOTT
CREATE UNIQUE INDEX "SCOTT"."PK_EMP" ON "SCOTT"."EMP" ("EMPNO")
  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 "SCOTT"."PK_EMP" NOPARALLEL;
-- new object type path: DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
-- CONNECT DPUSR
ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
  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 "USERS"  ENABLE;

Note:  Now attributes are ignored for table only.

Case 4: IGNORE SEGMENT ATTRIBUTES FOR INDEX

  [oracle@racs dp]$ impdp tables=scott.emp dumpfile=d1.dmp directory=dps sqlfile=emp_seg_ind.sql transform=segment_attributes:n:index

Import: Release 11.2.0.3.0 - Production on Tue Sep 24 09:53:51 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: dpusr
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "DPUSR"."SYS_SQL_FILE_TABLE_01" successfully loaded/unloaded
Starting "DPUSR"."SYS_SQL_FILE_TABLE_01":  dpusr/******** tables=scott.emp dumpfile=d1.dmp directory=dps sqlfile=emp_seg_ind.sql transform=segment_attributes:n:index
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
.....
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
Job "DPUSR"."SYS_SQL_FILE_TABLE_01" successfully completed at 09:54:01

[oracle@racs dp]$ vi emp_seg_ind.sql
 
  CREATE TABLE "SCOTT"."EMP"
   (    "EMPNO" NUMBER(4,0),
        "ENAME" VARCHAR2(10 BYTE),
        "JOB" VARCHAR2(9 BYTE),
        "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 DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;
-- new object type path: DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
-- CONNECT SCOTT
CREATE UNIQUE INDEX "SCOTT"."PK_EMP" ON "SCOTT"."EMP" ("EMPNO")
  ;
  ALTER INDEX "SCOTT"."PK_EMP" NOPARALLEL;
-- new object type path: DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
-- CONNECT DPUSR
ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
  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 "USERS"  ENABLE;

Note:  Now indexes are ignored attributes.

Case 5: IGNORE THE STORGE

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)

  [oracle@racs dp]$ impdp tables=scott.emp dumpfile=d1.dmp directory=dps sqlfile=emp_sto.sql transform=storage:n

Import: Release 11.2.0.3.0 - Production on Tue Sep 24 10:03:40 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: dpusr
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "DPUSR"."SYS_SQL_FILE_TABLE_01" successfully loaded/unloaded
Starting "DPUSR"."SYS_SQL_FILE_TABLE_01":  dpusr/******** tables=scott.emp dumpfile=d1.dmp directory=dps sqlfile=emp_sto.sql transform=storage:n
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
....
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
Job "DPUSR"."SYS_SQL_FILE_TABLE_01" successfully completed at 10:03:53

[oracle@racs dp]$ vi emp_sto.sql


  CREATE TABLE "SCOTT"."EMP"
   (    "EMPNO" NUMBER(4,0),
        "ENAME" VARCHAR2(10 BYTE),
        "JOB" VARCHAR2(9 BYTE),
        "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
  TABLESPACE "USERS" ;
-- new object type path: DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
-- CONNECT SCOTT
CREATE UNIQUE INDEX "SCOTT"."PK_EMP" ON "SCOTT"."EMP" ("EMPNO")
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  TABLESPACE "USERS" PARALLEL 1 ;

  ALTER INDEX "SCOTT"."PK_EMP" NOPARALLEL;
-- new object type path: DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
-- CONNECT DPUSR
ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
  TABLESPACE "USERS"  ENABLE;

Note: Now its ignored storage .


Case 6: 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.

For example,  EMP table pct space is 

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)

now pctspace=10  means it will initially allocates 10% extents and also for next extent, while importing the data.

[oracle@racs dp]$ impdp tables=scott.emp dumpfile=d1.dmp directory=dps sqlfile=emp_pct_tab.sql  transform=pctspace:10
Import: Release 11.2.0.3.0 - Production on Tue Sep 24 10:38:42 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: dpusr
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "DPUSR"."SYS_SQL_FILE_TABLE_01" successfully loaded/unloaded
Starting "DPUSR"."SYS_SQL_FILE_TABLE_01":  dpusr/******** tables=scott.emp dumpfile=d1.dmp directory=dps sqlfile=emp_pct_tab.sql transform=pctspace:10
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
Job "DPUSR"."SYS_SQL_FILE_TABLE_01" successfully completed at 10:38:55
[oracle@racs dp]$ vi emp_pct_tab.sql

CREATE TABLE "SCOTT"."EMP"
   (    "EMPNO" NUMBER(4,0),
        "ENAME" VARCHAR2(10 BYTE),
        "JOB" VARCHAR2(9 BYTE),
        "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 6554 NEXT 104858 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;
-- new object type path: DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
-- CONNECT SCOTT
CREATE UNIQUE INDEX "SCOTT"."PK_EMP" ON "SCOTT"."EMP" ("EMPNO")
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 6554 NEXT 104858 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 "SCOTT"."PK_EMP" NOPARALLEL;
-- new object type path: DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
-- CONNECT DPUSR
ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 6554 NEXT 104858 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"  ENABLE;

Now you can compare these values without pctspace option.

CASE 7: OID

SQL> SELECT SYS_OP_GUID() FROM DUAL;


SYS_OP_GUID()
--------------------------------
CF8C98BA272CA45CE0400C0A8D67761

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

SQL> CREATE TYPE APPtypes OID 'CF8C98BA272CA45CE0400C0A8D67761'
     AS OBJECT (attrib1 NUMBER);
     /

Type created.

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

TYPE_NAME                      TYPE_OID
------------------------------ --------------------------------
APPTYPE                        CF8C98BA272CA45CE0400C0A8D67761

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

expdp job_name=schemaexp1s schemas=scott include=type dumpfile=exp_types.dmp logfile=exp_type.log directory=dps 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_types.dmp logfile=exp_type.log directory=dps 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_types.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_types.dmp logfile=imp_exp_type.log directory=dps 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_types.dmp logfile=imp_exp_type.log directory=dps 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='APPTYPES';


TYPE_NAME                      TYPE_OID
------------------------------ --------------------------------
APPTYPE                        CF8CB70D75454DAE0400C0A8D671725

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_types.dmp logfile=imp_exp_type.log directory=dps 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
------------------------------ --------------------------------
APPTYPE                        CF8C98BA272CA45CE0400C0A8D67761
Posted by at 2 comments:
Labels:

Monday, September 23, 2013

SQLFILE IN DATAPUMP 11g

                                                               SQLFILE IN DATAPUMP 11g
SQLFILE : Default: There is no default
Purpose
Specifies a file into which all of the SQL DDL that Import would have executed, based on other parameters, is written.
Syntax and Description
SQLFILE=[directory_object:]file_name
The file_name specifies where the import job will write the DDL that would be executed during the job. The SQL is not actually executed, and the target system remains unchanged. The file is written to the directory object specified in the DIRECTORY parameter, unless another directory_object is explicitly specified here. Any existing file that has a name matching the one specified with this parameter is overwritten.
Note that passwords are not included in the SQL file. For example, if a CONNECT statement is part of the DDL that was executed, then it will be replaced by a comment with only the schema name shown. In the following example, the dashes (--) indicate that a comment follows, and the hr schema name is shown, but not the password.
-- CONNECT hr
Therefore, before you can execute the SQL file, you must edit it by removing the dashes indicating a comment and adding the password for the hr schema.
For Streams and other Oracle database options, anonymous PL/SQL blocks may appear within the SQLFILE output. They should not be executed directly.
Restrictions

  • If SQLFILE is specified, then the CONTENT parameter is ignored if it is set to either  ALL or  DATA_ONLY .
  • To perform a Data Pump Import to a SQL file using Oracle Automatic Storage Management (Oracle ASM), the SQLFILE parameter that you specify must include a directory object that does not use the Oracle ASM + notation. That is, the SQL file must be written to a disk file, not into the Oracle ASM storage.
  • The SQLFILE parameter cannot be used in conjunction with the QUERY parameter.



Step1:  Take the table backup of EMP table

[oracle@racs dp]$ expdp tables=EMP dumpfile=d1.dmp directory=dps

Export: Release 11.2.0.3.0 - Production on Mon Sep 23 09:56:44 2013

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

Username: dpusr
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Produc
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "DPUSR"."SYS_EXPORT_TABLE_01":  dpusr/******** tables=EMP dumpfile=d1.d
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 "DPUSR"."EMP"                               8.578 KB      14 rows
Master table "DPUSR"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for DPUSR.SYS_EXPORT_TABLE_01 is:
  /u01/apps/home/oracle/dp/d1.dmp
Job "DPUSR"."SYS_EXPORT_TABLE_01" successfully completed at 09:57:28

step2: Before import you can generate all DDL transctions as a file.  

You can open the file, check the tablespaces names and verify whether those tablespaces are exists or not. If not then remap the tablespace. 

 SEQUENCES:
As you know while importing if any object is there it will skip.  If your application works based on sequence number sometimes target and source sequence number may not be matched. In this case you can drop the target sequences  and create its .(To get the sequences  from source database, you can see sqlfile.).

 It saves lot of time. :) 


[oracle@racs dp]$ impdp tables=emp dumpfile=d1.dmp directory=dps sqlfile=t1.sql remap_Table=emp:T1

Import: Release 11.2.0.3.0 - Production on Mon Sep 23 09:59:08 2013

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

Username: dpusr
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "DPUSR"."SYS_SQL_FILE_TABLE_01" successfully loaded/unloaded
Starting "DPUSR"."SYS_SQL_FILE_TABLE_01":  dpusr/******** tables=emp dumpfile=d1.dmp directory=dps sqlfile=t1.sql remap_Table=emp:T1
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "DPUSR"."SYS_SQL_FILE_TABLE_01" successfully completed at 09:59:15



Note: Its not pointing about of number of records.  This script only shows object structure not records.


Now you have two options to import i.e either run the t1.sql script under dpusr or impdp to import the records
Step 3 A:  t1.sql script 

[oracle@racs dp]$ sqlplus dpusr

SQL*Plus: Release 11.2.0.3.0 Production on Mon Sep 23 10:00:12 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @t1.sql
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
DEPT                           TABLE
EMP                            TABLE
T1                             TABLE
TR                             VIEW


SQL> select * from t1;

no rows selected


Step3 B:  Using impdp

[oracle@racs dp]$ impdp tables=emp dumpfile=d1.dmp directory=dps  remap_Table=emp:T1

Import: Release 11.2.0.3.0 - Production on Mon Sep 23 10:01:27 2013

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

Username: dpusr
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "DPUSR"."SYS_IMPORT_TABLE_02" successfully loaded/unloaded
Starting "DPUSR"."SYS_IMPORT_TABLE_02":  dpusr/******** tables=emp dumpfile=d1.dmp directory=dps remap_Table=emp:T1
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "DPUSR"."T1"                                8.578 KB      14 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "DPUSR"."SYS_IMPORT_TABLE_02" successfully completed at 10:01:36

Posted by at No comments:
Labels:
Subscribe to: Posts (Atom)