Transform parameter allows you to alter object creation DDL while performing import. I have tried all the attempt to make the understanding of transform parameter easy.
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, 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, 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.
STORAGE – If the value is specified as y, 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, 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.
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.
Take an example,
Lets take an export for tablespace users.
expdp system/sys dumpfile=export:users.dmp tablespaces=users;
Now, take an import without actually performing import of objects on the database. Instead just create create the sql file which will contain all the DDL statements that an import would follow while actually performing import.
impdp system/sys tablespaces=users dumpfile=export:users.dmp sqlfile=export:ss1.sql
Here is the content of ss1.sql
— CONNECT SYSTEM
— new object type path is: TABLE_EXPORT/TABLE/TABLE
CREATE TABLE “SCOTT”.”DEPT”
( “DEPTNO” NUMBER(2,0),
“DNAME” VARCHAR2(14),
“LOC” VARCHAR2(13)
) 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)
TABLESPACE “USERS” ;
— new object type path is: TABLE_EXPORT/TABLE/TABLE
CREATE TABLE “SCOTT”.”DEPT”
( “DEPTNO” NUMBER(2,0),
“DNAME” VARCHAR2(14),
“LOC” VARCHAR2(13)
) 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)
TABLESPACE “USERS” ;
CREATE TABLE “SCOTT”.”EMP”
( “EMPNO” NUMBER(4,0),
“ENAME” VARCHAR2(10),
“JOB” VARCHAR2(9),
“MGR” NUMBER(4,0),
“HIREDATE” DATE,
“SAL” NUMBER(7,2),
“COMM” NUMBER(7,2),
“DEPTNO” NUMBER(2,0)
) 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)
TABLESPACE “USERS” ;
// content trimmed //
( “EMPNO” NUMBER(4,0),
“ENAME” VARCHAR2(10),
“JOB” VARCHAR2(9),
“MGR” NUMBER(4,0),
“HIREDATE” DATE,
“SAL” NUMBER(7,2),
“COMM” NUMBER(7,2),
“DEPTNO” NUMBER(2,0)
) 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)
TABLESPACE “USERS” ;
// content trimmed //
Here all the DDL statement contains all the segment attributes. Lets use transform attribute to skip segment attributes in total.
[oracle@host1 exp]$ impdp system/sys dumpfile=export:users.dmp tablespaces=users sqlfile=export:s1.sql transform=segment_attributes:n
Here is the content of s1.sql
— CONNECT SYSTEM
— new object type path is: TABLE_EXPORT/TABLE/TABLE
CREATE TABLE “SCOTT”.”DEPT”
( “DEPTNO” NUMBER(2,0),
“DNAME” VARCHAR2(14),
“LOC” VARCHAR2(13)
) ;
— new object type path is: TABLE_EXPORT/TABLE/TABLE
CREATE TABLE “SCOTT”.”DEPT”
( “DEPTNO” NUMBER(2,0),
“DNAME” VARCHAR2(14),
“LOC” VARCHAR2(13)
) ;
CREATE TABLE “SCOTT”.”EMP”
( “EMPNO” NUMBER(4,0),
“ENAME” VARCHAR2(10),
“JOB” VARCHAR2(9),
“MGR” NUMBER(4,0),
“HIREDATE” DATE,
“SAL” NUMBER(7,2),
“COMM” NUMBER(7,2),
“DEPTNO” NUMBER(2,0)
) ;
( “EMPNO” NUMBER(4,0),
“ENAME” VARCHAR2(10),
“JOB” VARCHAR2(9),
“MGR” NUMBER(4,0),
“HIREDATE” DATE,
“SAL” NUMBER(7,2),
“COMM” NUMBER(7,2),
“DEPTNO” NUMBER(2,0)
) ;
Now, use transform attribute to skip only the storage clause.
[oracle@host1 exp]$ impdp system/sys dumpfile=export:users.dmp tablespaces=users sqlfile=export:s1.sql transform=storage:n
Here is the content of s1.sql
— CONNECT SYSTEM
— new object type path is: TABLE_EXPORT/TABLE/TABLE
CREATE TABLE “SCOTT”.”DEPT”
( “DEPTNO” NUMBER(2,0),
“DNAME” VARCHAR2(14),
“LOC” VARCHAR2(13)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
TABLESPACE “USERS” ;
— new object type path is: TABLE_EXPORT/TABLE/TABLE
CREATE TABLE “SCOTT”.”DEPT”
( “DEPTNO” NUMBER(2,0),
“DNAME” VARCHAR2(14),
“LOC” VARCHAR2(13)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
TABLESPACE “USERS” ;
CREATE TABLE “SCOTT”.”EMP”
( “EMPNO” NUMBER(4,0),
“ENAME” VARCHAR2(10),
“JOB” VARCHAR2(9),
“MGR” NUMBER(4,0),
“HIREDATE” DATE,
“SAL” NUMBER(7,2),
“COMM” NUMBER(7,2),
“DEPTNO” NUMBER(2,0)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
TABLESPACE “USERS” ;
( “EMPNO” NUMBER(4,0),
“ENAME” VARCHAR2(10),
“JOB” VARCHAR2(9),
“MGR” NUMBER(4,0),
“HIREDATE” DATE,
“SAL” NUMBER(7,2),
“COMM” NUMBER(7,2),
“DEPTNO” NUMBER(2,0)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
TABLESPACE “USERS” ;
Now, lets try to alter buffer_pool parameter storage clause. Here is a simple demo:
SQL> create table hr.tes4(c1 number) storage(buffer_pool keep);
Table created
[oracle@host1 exp]$ expdp hr/hr tables=tes4 dumpfile=tes4.dmp directory=export
Export: Release 10.2.0.1.0 – Production on Monday, 11 March, 2013 15:07:58
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options
Starting “HR”.”SYS_EXPORT_TABLE_01″: hr/******** tables=tes4 dumpfile=export.tes4.dmp directory=export
Estimate in progress using BLOCKS method…
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported “HR”.”TES4″ 0 KB 0 rows
Master table “HR”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for HR.SYS_EXPORT_TABLE_01 is:
/home/oracle/exp/export.tes4.dmp
Job “HR”.”SYS_EXPORT_TABLE_01″ successfully completed at 15:08:01
With the Partitioning, OLAP and Data Mining options
Starting “HR”.”SYS_EXPORT_TABLE_01″: hr/******** tables=tes4 dumpfile=export.tes4.dmp directory=export
Estimate in progress using BLOCKS method…
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported “HR”.”TES4″ 0 KB 0 rows
Master table “HR”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for HR.SYS_EXPORT_TABLE_01 is:
/home/oracle/exp/export.tes4.dmp
Job “HR”.”SYS_EXPORT_TABLE_01″ successfully completed at 15:08:01
[oracle@host1 exp]$ impdp hr/hr tables=tes4 dumpfile=export.tes4.dmp directory=export sqlfile=s1.sql
Import: Release 10.2.0.1.0 – Production on Monday, 11 March, 2013 15:08:15
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options
Master table “HR”.”SYS_SQL_FILE_TABLE_01″ successfully loaded/unloaded
Starting “HR”.”SYS_SQL_FILE_TABLE_01″: hr/******** tables=tes4 dumpfile=export.tes4.dmp directory=export sqlfile=s1.sql
Processing object type TABLE_EXPORT/TABLE/TABLE
Job “HR”.”SYS_SQL_FILE_TABLE_01″ successfully completed at 15:08:17
With the Partitioning, OLAP and Data Mining options
Master table “HR”.”SYS_SQL_FILE_TABLE_01″ successfully loaded/unloaded
Starting “HR”.”SYS_SQL_FILE_TABLE_01″: hr/******** tables=tes4 dumpfile=export.tes4.dmp directory=export sqlfile=s1.sql
Processing object type TABLE_EXPORT/TABLE/TABLE
Job “HR”.”SYS_SQL_FILE_TABLE_01″ successfully completed at 15:08:17
Here is the content of s1.sql.
— CONNECT HR
— new object type path is: TABLE_EXPORT/TABLE/TABLE
CREATE TABLE “HR”.”TES4″
( “C1” NUMBER
) 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 KEEP)
TABLESPACE “USERS” ;
— new object type path is: TABLE_EXPORT/TABLE/TABLE
CREATE TABLE “HR”.”TES4″
( “C1” NUMBER
) 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 KEEP)
TABLESPACE “USERS” ;
Now take an import again but use transform attribute this time.
[oracle@host1 exp]$ impdp hr/hr tables=tes4 dumpfile=export.tes4.dmp directory=export sqlfile=s1.sql transform=storage:n;
Import: Release 10.2.0.1.0 – Production on Monday, 11 March, 2013 15:11:08
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options
Master table “HR”.”SYS_SQL_FILE_TABLE_01″ successfully loaded/unloaded
Starting “HR”.”SYS_SQL_FILE_TABLE_01″: hr/******** tables=tes4 dumpfile=export.tes4.dmp directory=export sqlfile=s1.sql transform=storage:n
Processing object type TABLE_EXPORT/TABLE/TABLE
Job “HR”.”SYS_SQL_FILE_TABLE_01″ successfully completed at 15:11:09
With the Partitioning, OLAP and Data Mining options
Master table “HR”.”SYS_SQL_FILE_TABLE_01″ successfully loaded/unloaded
Starting “HR”.”SYS_SQL_FILE_TABLE_01″: hr/******** tables=tes4 dumpfile=export.tes4.dmp directory=export sqlfile=s1.sql transform=storage:n
Processing object type TABLE_EXPORT/TABLE/TABLE
Job “HR”.”SYS_SQL_FILE_TABLE_01″ successfully completed at 15:11:09
[oracle@host1 exp]$ cat s1.sql
— CONNECT HR
— new object type path is: TABLE_EXPORT/TABLE/TABLE
CREATE TABLE “HR”.”TES4″
( “C1” NUMBER
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
TABLESPACE “USERS” ;
— CONNECT HR
— new object type path is: TABLE_EXPORT/TABLE/TABLE
CREATE TABLE “HR”.”TES4″
( “C1” NUMBER
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
TABLESPACE “USERS” ;
Here table will be imported using default storage attribute for buffer pool.
Lets take another example using pctspace attributes.
SQL>create tablespace test datafile ‘/u01/app/oracle/oradata/orcl/test01.dbf’ size 10m
Tablespace created.
[oracle@host1 exp]$ expdp system/sys full=y dumpfile=export:test.dmp parfile=par.txt
Here is the content of par.txt
include=tablespace:”in’TEST'”
[oracle@host1 ~]$ impdp system/sys dumpfile=export:test.dmp parfile=par.txt sqlfile=export:ts.sql
Import: Release 10.2.0.1.0 – Production on Monday, 11 March, 2013 15:17:01
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options
Master table “SYSTEM”.”SYS_SQL_FILE_FULL_01″ successfully loaded/unloaded
Starting “SYSTEM”.”SYS_SQL_FILE_FULL_01″: system/******** dumpfile=export:test.dmp parfile=par.txt sqlfile=export:ts.sql
Processing object type DATABASE_EXPORT/TABLESPACE
Job “SYSTEM”.”SYS_SQL_FILE_FULL_01″ successfully completed at 15:17:03
With the Partitioning, OLAP and Data Mining options
Master table “SYSTEM”.”SYS_SQL_FILE_FULL_01″ successfully loaded/unloaded
Starting “SYSTEM”.”SYS_SQL_FILE_FULL_01″: system/******** dumpfile=export:test.dmp parfile=par.txt sqlfile=export:ts.sql
Processing object type DATABASE_EXPORT/TABLESPACE
Job “SYSTEM”.”SYS_SQL_FILE_FULL_01″ successfully completed at 15:17:03
[oracle@host1 exp]$ cat ts.sql
— CONNECT SYSTEM
— new object type path is: DATABASE_EXPORT/TABLESPACE
CREATE TABLESPACE “TEST” DATAFILE
‘/u01/app/oracle/oradata/orcl/test01.dbf’ SIZE 10485760
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;
— new object type path is: DATABASE_EXPORT/TABLESPACE
CREATE TABLESPACE “TEST” DATAFILE
‘/u01/app/oracle/oradata/orcl/test01.dbf’ SIZE 10485760
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;
Now take the import again, but using transform attribute this time.
[oracle@host1 exp]$ impdp system/sys dumpfile=export:test.dmp parfile=/home/oracle/par.txt sqlfile=export:ts.sqltransform=pctspace:10
Import: Release 10.2.0.1.0 – Production on Monday, 11 March, 2013 15:18:23
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options
Master table “SYSTEM”.”SYS_SQL_FILE_FULL_01″ successfully loaded/unloaded
Starting “SYSTEM”.”SYS_SQL_FILE_FULL_01″: system/******** dumpfile=export:test.dmp parfile=/home/oracle/par.txt sqlfile=export:ts.sql transform=pctspace:10
Processing object type DATABASE_EXPORT/TABLESPACE
Job “SYSTEM”.”SYS_SQL_FILE_FULL_01″ successfully completed at 15:18:24
With the Partitioning, OLAP and Data Mining options
Master table “SYSTEM”.”SYS_SQL_FILE_FULL_01″ successfully loaded/unloaded
Starting “SYSTEM”.”SYS_SQL_FILE_FULL_01″: system/******** dumpfile=export:test.dmp parfile=/home/oracle/par.txt sqlfile=export:ts.sql transform=pctspace:10
Processing object type DATABASE_EXPORT/TABLESPACE
Job “SYSTEM”.”SYS_SQL_FILE_FULL_01″ successfully completed at 15:18:24
[oracle@host1 exp]$ cat ts.sql
— CONNECT SYSTEM
— new object type path is: DATABASE_EXPORT/TABLESPACE
CREATE TABLESPACE “TEST” DATAFILE
‘/u01/app/oracle/oradata/orcl/test01.dbf’ SIZE 1048576
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;
— CONNECT SYSTEM
— new object type path is: DATABASE_EXPORT/TABLESPACE
CREATE TABLESPACE “TEST” DATAFILE
‘/u01/app/oracle/oradata/orcl/test01.dbf’ SIZE 1048576
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;
Have a look at the bold text . Here we have changed datafile size to 10% of original datafile size.
No comments:
Post a Comment