oracle 数据泵排除表,【impdp】使用impdp工具排除特定表的导入

在《【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/secsec@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                             TABLET1                             TABLET3                             TABLE2.使用expdp工具生成sec用户的备份文件ora10g@asdlabdb01 /db_backup/dpump_dir$ expdp system/oracle directory=dpump_dir dumpfile=sec.dmp schemas=secExport: Release 10.2.0.3.0 - 64bit Production on Thursday, 01 September, 2010 22:34:30Copyright (c) 2003, 2005, Oracle.  All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit ProductionWith the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine optionsStarting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** directory=dpump_dir dumpfile=sec.dmp schemas=secEstimate in progress using BLOCKS method...Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 192 KBProcessing object type SCHEMA_EXPORT/USERProcessing object type SCHEMA_EXPORT/SYSTEM_GRANTProcessing object type SCHEMA_EXPORT/ROLE_GRANTProcessing object type SCHEMA_EXPORT/DEFAULT_ROLEProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing 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 rowsMaster table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded******************************************************************************Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:/db_backup/dpump_dir/sec.dmpJob "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 02:34:363.删除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:20Copyright (c) 2003, 2005, Oracle.  All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit ProductionWith the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine optionsMaster table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloadedStarting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** directory=dpump_dir dumpfile=sec.dmp exclude=table:in('T1')Processing object type SCHEMA_EXPORT/USERProcessing object type SCHEMA_EXPORT/SYSTEM_GRANTProcessing object type SCHEMA_EXPORT/ROLE_GRANTProcessing object type SCHEMA_EXPORT/DEFAULT_ROLEProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/TABLE/TABLEProcessing object type SCHEMA_EXPORT/TABLE/TABLE_DATA. . imported "SEC"."T2"                                  4.914 KB       1 rows. . imported "SEC"."T3"                                  4.914 KB       1 rowsJob "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 02:35:235.小结在Oracle 10g时代，推荐选用expdp/impd工具完成逻辑备份及数据迁移，该工具的主要特点是：迅速、便捷！Good luck.secooler10.09.01-- The End --