《oracle----【EXPDP】使用EXPDP工具的 EXCLUDE选项过滤掉不关心的数据》由会员分享，可在线阅读，更多相关《oracle----【EXPDP】使用EXPDP工具的 EXCLUDE选项过滤掉不关心的数据（10页珍藏版）》请在金锄头文库上搜索。
1、【EXPDP】使用EXPDP工具的 EXCLUDE选项过滤掉不关心的数据库对象上一篇 / 下一篇 2010-03-08 22:54:51 / 个人分类：备份与恢复查看( 952 )/ 评论( 2 ) / 评分( 5 / 0 )使用EXPDP逻辑备份工具的EXCLUDE选项可以指定那类数据库对象不被导出，EXPDP工具的前身EXP如果想要完成同样的任务非常的困难。我们以排除部分表为例看一下这个选项带给我们的便利。如果在命令行中完成备份，特殊字符的转义需要特别注意（我这里使用的是Linux操作系统）。1.EXPDP帮助中的描述信息ora10gsecDB /expdp$ expdp -helpEXC
2、LUDE Exclude specific object types, e.g. EXCLUDE=TABLE:EMP.2.创建directory数据库对象，并将读写权限授予sec用户sysora10g create or replace directory dump_dir as /expdp;Directory created.sysora10g grant read,write on directory dump_dir to sec;Grant succeeded.3.确认操作系统信息ora10gsecDB /expdp$ uname -aLinux secDB 2.6.18-128.e
3、l5 #1 SMP Wed Dec 17 11:41:38 EST 2008 x86_64 x86_64 x86_64 GNU/Linux4.在sec用户下创建几张表用于后续的测试创建三张表T1、T2和T3，每张表中初始化一条数据secora10g create table t1 (x int);secora10g insert into t1 values (1);secora10g create table t2 (x int);secora10g insert into t2 values (2);secora10g create table t3 (x int);secora10g i
4、nsert into t3 values (3);secora10g commit;5.为了与后面的比较，先全用户导出ora10gsecDB /expdp$ rm -f sec.dmp sec.logora10gsecDB /expdp$ expdp sec/sec directory=dump_dir dumpfile=sec.dmp logfile=sec.logExport: Release 10.2.0.3.0 - 64bit Production on Monday, 08 March, 2010 9:59:25Copyright (c) 2003, 2005, Oracle. Al
5、l 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 SEC.SYS_EXPORT_SCHEMA_01: sec/* directory=dump_dir dumpfile=sec.dmp logfile=sec.logEstimate in
6、 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
7、/DEFAULT_ROLEProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/TABLE/TABLEProcessing object type SCHEMA_EXPORT/TABLE/INDEX/INDEXProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS
8、/INDEX_STATISTICSProcessing object type SCHEMA_EXPORT/TABLE/COMMENT. . 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 SEC.SYS_EXPORT_SCHEMA_01 successfully loaded/unloaded*Dump file set for SEC.SYS_EXPORT_SCHEMA_01 is: /expdp/sec.dmp
9、Job SEC.SYS_EXPORT_SCHEMA_01 successfully completed at 09:59:326.排除T1表进行备份ora10gsecDB /expdp$ rm -f sec.dmp sec.logora10gsecDB /expdp$ expdp sec/sec directory=dump_dir dumpfile=sec.dmp logfile=sec.log EXCLUDE=TABLE:IN(T1)Export: Release 10.2.0.3.0 - 64bit Production on Monday, 08 March, 2010 10:02:0
10、3Copyright (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 SEC.SYS_EXPORT_SCHEMA_01: sec/* directory=dump_dir dumpfi
11、le=sec.dmp logfile=sec.log EXCLUDE=TABLE:IN(T1)Estimate in progress using BLOCKS method.Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 128 KBProcessing object type SCHEMA_EXPORT/USERProcessing object type SCHEMA_EXPORT/SYSTEM_GRANTProcessing object type SC
12、HEMA_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/INDEX/INDEXProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINTP
13、rocessing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type SCHEMA_EXPORT/TABLE/COMMENT. . exported SEC.T2 4.914 KB 1 rows. . exported SEC.T3 4.914 KB 1 rowsMaster table SEC.SYS_EXPORT_SCHEMA_01 successfully loaded/unloaded*Dump file set for SEC.SYS_EXPORT_SCHEM
14、A_01 is: /expdp/sec.dmpJob SEC.SYS_EXPORT_SCHEMA_01 successfully completed at 10:02:10排除表T1后T2和T3表被成功导出。7.排除多张表进行备份以排除表T1和T2两张表为例进行演示ora10gsecDB /expdp$ rm -f sec.dmp sec.logora10gsecDB /expdp$ expdp sec/sec directory=dump_dir dumpfile=sec.dmp logfile=sec.log EXCLUDE=TABLE:IN(T1,T2)Export: Release 10.2.0.3.0 - 64bit Production on Monday, 08 March, 2010 10:03:17Copyright (c) 2003, 2005, Oracle. All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Producti
