使用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 --

【IMPDP】使用 TRANSFORM選項去掉表空間和儲存子句

相關文章