【IMPDP】使用 TRANSFORM选项去掉表空间和存储子句

使用IMPDP工具完成数据导入时，会按照dump文件中有关的存储的参数信息完成数据的导入。很多情况下我们希望按照被导入用户的默认参数完成数据的导入，此时我们可以使用IMPDP的TRANSFORM参数辅助完成。

 

1.IMPDP的TRANSFORM参数描述

secooler@secDB /expdp$ impdp help=y

……省略……

TRANSFORM

Metadata transform. to apply to applicable objects.

Valid keywords are: OID, PCTSPACE, SEGMENT_ATTRIBUTES and STORAGE.

……省略……

 

2.创建一个测试表T

sec@11gR2> create table t (x varchar2(8));

 

Table created.

 

sec@11gR2> insert into t values ('secooler');

 

1 row created.

 

sec@11gR2> commit;

 

Commit complete.

 

3.使用EXPDP生成表T的逻辑备份文件

secooler@secDB /expdp$ expdp sec/sec directory=expdp_dir dumpfile=sec_expdp.dmp logfile=sec_expdp.log tables=t

 

Export: Release 11.2.0.1.0 - Production on Thu May 13 09:32:44 2010

 

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

 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining,

Oracle Database Vault and Real Application Testing options

Starting "SEC"."SYS_EXPORT_TABLE_01":  sec/******** directory=expdp_dir dumpfile=sec_expdp.dmp logfile=sec_expdp.log tables=t

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

. . exported "SEC"."T"                                   5.007 KB       1 rows

Master table "SEC"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SEC.SYS_EXPORT_TABLE_01 is:

  /expdp/sec_expdp.dmp

Job "SEC"."SYS_EXPORT_TABLE_01" successfully completed at 09:32:57

 

4.生成dump文件中的SQL语句

1）使用SQLFILE参数生成SQL创建语句

secooler@secDB /expdp$ impdp sec/sec directory=expdp_dir dumpfile=sec_expdp.dmp sqlfile=sec_expdp.sql

 

Import: Release 11.2.0.1.0 - Production on Thu May 13 09:33:23 2010

 

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

 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining,

Oracle Database Vault and Real Application Testing options

Master table "SEC"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded

Starting "SEC"."SYS_SQL_FILE_FULL_01":  sec/******** directory=expdp_dir dumpfile=sec_expdp.dmp sqlfile=sec_expdp.sql

Processing object type TABLE_EXPORT/TABLE/TABLE

Job "SEC"."SYS_SQL_FILE_FULL_01" successfully completed at 09:33:26

 

2）查看sec_expdp.sql文件获得SQL创建语句

secooler@secDB /expdp$ cat sec_expdp.sql

-- CONNECT SEC

ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';

ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';

ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';

ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';

ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';

ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';

-- new object type path: TABLE_EXPORT/TABLE/TABLE

CREATE TABLE "SEC"."T"

   (    "X" VARCHAR2(8 BYTE)

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

 

可见，此时包含了大量的存储参数及表空间参数。

 

5.使用TRANSFORM去掉表空间和存储子句

secooler@secDB /expdp$ impdp sec/sec directory=expdp_dir dumpfile=sec_expdp.dmp sqlfile=sec_expdp.sql TRANSFORM=segment_attributes:n

 

Import: Release 11.2.0.1.0 - Production on Thu May 13 09:34:12 2010

 

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

 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining,

Oracle Database Vault and Real Application Testing options

Master table "SEC"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded

Starting "SEC"."SYS_SQL_FILE_FULL_01":  sec/******** directory=expdp_dir dumpfile=sec_expdp.dmp sqlfile=sec_expdp.sql TRANSFORM=segment_attributes:n

Processing object type TABLE_EXPORT/TABLE/TABLE

Job "SEC"."SYS_SQL_FILE_FULL_01" successfully completed at 09:34:14

 

再次查看生成的穿件SQL语句：

secooler@secDB /expdp$ cat sec_expdp.sql

-- CONNECT SEC

ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';

ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';

ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';

ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';

ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';

ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';

-- new object type path: TABLE_EXPORT/TABLE/TABLE

CREATE TABLE "SEC"."T"

   (    "X" VARCHAR2(8 BYTE)

   ) ;

 

此时生成的表T创建语句非常的简介，没错，就是这么简单。

 

6.小结

使用TRANSFORM选项可以完成去掉表空间和存储子句的目的，这样我们便可以控制导入时按照目标默认的参数。

我们的目标：所有要完成的任务都要在自己的掌控之中，UNDER CONTROL！

 

Good luck.

 

secooler

19.05.12

 

-- The End --

 

 

源文档 <http://space.itpub.net/519536/viewspace-662584>