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

【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@ora10g>createorreplacedirectorydump_diras'/expdp';Directorycreated.sys@ora10g>grantread,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@ora10g>createtablet1(xint);sec@ora10g>insertintot1values(1);sec@ora10g>createtablet2(xint);sec@ora10g>insertintot2values(2);sec@ora10g>createtablet3(xint);sec@ora10g>insertintot3values(3);sec@ora10g>commit;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,OLAPandDataMiningScoringEngineoptionsStarting"SEC"."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..exported"SEC"."T1"4.914KB1rows..exported"SEC"."T2"4.914KB1rows..exported"SEC"."T3"4.914KB1rowsMastertable"SEC"."SYS_EXPORT_SCHEMA_01"successfullyloaded/unloaded******************************************************************************DumpfilesetforSEC.SYS_EXPORT_SCHEMA_01is:/expdp/sec.dmpJob"SEC"."SYS_EXPORT_SCHEMA_01"successfullycompletedat09: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,OLAPandDataMiningScoringEngineoptionsStarting"SEC"."SYS_EXPORT_SCHEMA_01":sec/********directory=dump_dirdumpfile=sec.dmplogfile=sec.logEXCLUDE=TABLE:"IN('T1')"EstimateinprogressusingBLOCKSmethod...ProcessingobjecttypeSCHEMA_EXPORT/TABLE/TABLE_DATATotalestimationusingBLOCKSmethod:128KBProcessingobjecttypeSCHEMA_E...