oracle----【EXPDP】使用EXPDP工具的 EXCLUDE选项过滤掉不关心的数据

【EXPDP】使用EXPDP工具的EXCLUDE选项过滤掉不关心的数据库对象上一篇/下一篇2010-03-0822:54:51/个人分类：备份与恢复查看(952)/评论(2)/评分(5/0)使用EXPDP逻辑备份工具的EXCLUDE选项可以指定那类数据库对象不被导出，EXPDP工具的前身EXP如果想要完成同样的任务非常的困难。我们以排除部分表为例看一下这个选项带给我们的便利。如果在命令行中完成备份，特殊字符的转义需要特别注意（我这里使用的是Linux操作系统）。1.EXPDP帮助中的描述信息ora10g@secDB/expdp$expdp-help……EXCLUDEExcludespecificobjecttypes,e.g.EXCLUDE=TABLE:EMP.……2.创建directory数据库对象，并将读写权限授予sec用户sys@ora10gcreateorreplacedirectorydump_diras'/expdp';Directorycreated.sys@ora10ggrantread,writeondirectorydump_dirtosec;Grantsucceeded.3.确认操作系统信息ora10g@secDB/expdp$uname-aLinuxsecDB2.6.18-128.el5#1SMPWedDec1711:41:38EST2008x86_64x86_64x86_64GNU/Linux4.在sec用户下创建几张表用于后续的测试创建三张表T1、T2和T3，每张表中初始化一条数据sec@ora10gcreatetablet1(xint);sec@ora10ginsertintot1values(1);sec@ora10gcreatetablet2(xint);sec@ora10ginsertintot2values(2);sec@ora10gcreatetablet3(xint);sec@ora10ginsertintot3values(3);sec@ora10gcommit;5.为了与后面的比较，先全用户导出ora10g@secDB/expdp$rm-fsec.dmpsec.logora10g@secDB/expdp$expdpsec/secdirectory=dump_dirdumpfile=sec.dmplogfile=sec.logExport:Release10.2.0.3.0-64bitProductiononMonday,08March,20109:59:25Copyright(c)2003,2005,Oracle.Allrightsreserved.Connectedto:OracleDatabase10gEnterpriseEditionRelease10.2.0.3.0-64bitProductionWiththePartitioning,OracleLabelSecurity,OLAPandDataMiningScoringEngineoptionsStartingSEC.SYS_EXPORT_SCHEMA_01:sec/********directory=dump_dirdumpfile=sec.dmplogfile=sec.logEstimateinprogressusingBLOCKSmethod...ProcessingobjecttypeSCHEMA_EXPORT/TABLE/TABLE_DATATotalestimationusingBLOCKSmethod:192KBProcessingobjecttypeSCHEMA_EXPORT/USERProcessingobjecttypeSCHEMA_EXPORT/SYSTEM_GRANTProcessingobjecttypeSCHEMA_EXPORT/ROLE_GRANTProcessingobjecttypeSCHEMA_EXPORT/DEFAULT_ROLEProcessingobjecttypeSCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessingobjecttypeSCHEMA_EXPORT/TABLE/TABLEProcessingobjecttypeSCHEMA_EXPORT/TABLE/INDEX/INDEXProcessingobjecttypeSCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessingobjecttypeSCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessingobjecttypeSCHEMA_EXPORT/TABLE/COMMENT..exportedSEC.T14.914KB1rows..exportedSEC.T24.914KB1rows..exportedSEC.T34.914KB1rowsMastertableSEC.SYS_EXPORT_SCHEMA_01successfullyloaded/unloaded******************************************************************************DumpfilesetforSEC.SYS_EXPORT_SCHEMA_01is:/expdp/sec.dmpJobSEC.SYS_EXPORT_SCHEMA_01successfullycompletedat09:59:326.排除T1表进行备份ora10g@secDB/expdp$rm-fsec.dmpsec.logora10g@secDB/expdp$expdpsec/secdirectory=dump_dirdumpfile=sec.dmplogfile=sec.logEXCLUDE=TABLE:\IN\(\'T1\'\)\Export:Release10.2.0.3.0-64bitProductiononMonday,08March,201010:02:03Copyright(c)2003,2005,Oracle.Allrightsreserved.Connectedto:OracleDatabase10gEnterpriseEditionRelease10.2.0.3.0-64bitProductionWiththePartitioning,OracleLabelSecurity,OLAPandDataMiningScoringEngineoptionsStartingSEC.SYS_EXPORT_SCHEMA_01:sec/********directory=dump_dirdumpfile=sec.dmplogfile=sec.logEXCLUDE=TABLE:IN('T1')EstimateinprogressusingBLOCKSmethod...ProcessingobjecttypeSCHEMA_EXPORT/TABLE/TABLE_DATATotalestimationusingBLOCKSmethod:128KBProcessingobjecttypeSCHEMA_EXPORT/USERProcessingobjecttypeSCHEMA_EXPORT/SYSTEM_GRANTProcessingobjecttypeSCHEMA_EXPORT/ROLE_GRANTProcessingobjecttypeSCHEMA_EXPORT/DEFAULT_ROLEProcessingobjecttypeSCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessingobjecttypeSCHEMA_EXPORT/TABLE/TABLEProcessingobjecttypeSCHEMA_EXPORT/TABLE/INDEX/INDEXProcessingobjecttypeSCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessingobjecttypeSCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessingobjecttypeSCHEMA_EXPORT/TABLE/COMMENT..exportedSEC.T24.914KB1rows..exportedSEC.T34.914KB1rowsMastertableSEC.SYS_EXPORT_SCHEMA_01successfullyloaded/unloaded******************************************************************************DumpfilesetforSEC.SYS_EXPORT_SCHEMA_01is:/expdp/sec.dmpJobSEC.SYS_EXPORT_SCHEMA_01successfullycompletedat10:02:10排除表T1后T2和T3表被成功导出。7.排除多张表进行备份以排除表T1和T2两张表为例进行演示ora10g@secDB/expdp$rm-fsec.dmpsec.logora10g@secDB/expdp$expdpsec/secdirectory=dump_dirdumpfile=sec.dmplogfile=sec.logEXCLUDE=TABLE:\IN\(\'T1\',\'T2\'\)\Export:Release10.2.0.3.0-64bitProductiononMonday,08March,201010:03:17Copyright(c)2003,2005,Oracle.Allrightsreserved.Connectedto:OracleDatabase10gEnterpriseEditionRelease10.2.0.3.0-64bitProductionWiththePartitioning,OracleLabelSecurity,OLAPandDataMiningScoringEngineoptionsStartingSEC.SYS_EXPORT_SCHEMA_01:sec/********directory=dump_dirdumpfile=sec.dmplogfile=sec.logEXCLUDE=TABLE:IN('T1','T2')EstimateinprogressusingBLOCKSmethod...ProcessingobjecttypeSCHEMA_EXPORT/TABLE/TABLE_DATATotalestimationusingBLOCKSmethod:64KBProcessingobjecttypeSCHEMA_EXPORT/USERProcessingobjecttypeSCHEMA_EXPORT/SYSTEM_GRANTProcessingobjecttypeSCHEMA_EXPORT/ROLE_GRANTProcessingobjecttypeSCHEMA_EXPORT/DEFAULT_ROLEProcessingobjecttypeSCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessingobjecttypeSCHEMA_EXPORT/TABLE/TABLEProcessingobjecttypeSCHEMA_EXPORT/TABLE/INDEX/INDEXProcessingobjecttypeSCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessingobjecttypeSCHEMA_EXPORT/TABLE/INDEX/STATISTIC