在《【imp】使用imp工具遷移資料時迂迴地排除特定表的匯入》（http://space.itpub.net/519536/viewspace-672170）文中提到使用imp工具迂迴的完成排除特定表匯入問題。使用impdp工具可以非常容易的實現。

1.環境準備
1）建立使用者sec並給予
sys@ora10g> drop user sec cascade;
sys@ora10g> create user sec identified by sec default tablespace TBS_SEC_D;
sys@ora10g> grant dba to sec;

2）在sec使用者下建立三張表，為每張表中初始化一條資料
sys@ora10g> conn sec/sec
sec@ora10g> create table t1 (x int);
sec@ora10g> create table t2 (x int);
sec@ora10g> create table t3 (x int);
sec@ora10g> insert into t1 values(1);
sec@ora10g> insert into t2 values(2);
sec@ora10g> insert into t3 values(3);
sec@ora10g> commit;

sec@ora10g> select * from tab;

TNAME                          TABTYPE CLUSTERID
------------------------------ ------- ----------
T2                             TABLE
T1                             TABLE
T3                             TABLE

2.使用expdp工具生成sec使用者的備份檔案
ora10g@asdlabdb01 /db_backup/dpump_dir$ expdp system/oracle directory=dpump_dir dumpfile=sec.dmp schemas=sec

Export: Release 10.2.0.3.0 - 64bit Production on Thursday, 01 September, 2010 22:34:30

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** directory=dpump_dir dumpfile=sec.dmp schemas=sec
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported "SEC"."T1"                                  4.914 KB       1 rows
. . exported "SEC"."T2"                                  4.914 KB       1 rows
. . exported "SEC"."T3"                                  4.914 KB       1 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/db_backup/dpump_dir/sec.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 02:34:36

3.刪除sec使用者
sys@ora10g> drop user sec cascade;

User dropped.

4.使用impdp工具完成匯入
1）匯入操作
ora10g@asdlabdb01 /db_backup/dpump_dir$ impdp system/oracle directory=dpump_dir dumpfile=sec.dmp exclude=table:"in('T1')"

Import: Release 10.2.0.3.0 - 64bit Production on Thursday, 01 September, 2010 22:35:20

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** directory=dpump_dir dumpfile=sec.dmp exclude=table:in('T1')
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SEC"."T2"                                  4.914 KB       1 rows
. . imported "SEC"."T3"                                  4.914 KB       1 rows
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 02:35:23

5.小結
在Oracle 10g時代，推薦選用expdp/impd工具完成邏輯備份及資料遷移，該工具的主要特點是：迅速、便捷！

Good luck.

secooler
10.09.01

-- The End --

【impdp】使用impdp工具排除特定表的匯入

相關文章