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

《oracle----【EXPDP】使用EXPDP工具的 EXCLUDE选项过滤掉不关心的数据.docx》由会员分享，可在线阅读，更多相关《oracle----【EXPDP】使用EXPDP工具的 EXCLUDE选项过滤掉不关心的数据.docx（8页珍藏版）》请在优知文库上搜索。

1、【EXPDP】使用EXpDP工具的EXc1.uDE选项过滤掉不关心的数据库对象上一It下一32010-034)822:5:51个人“丁：备付与帙夏OI952IZ讦论(2)/iP(,/)使用BMP逻辑各件工具的EXC1.UDE选项可以指定那类效务串对蠹不被导出,EXPDP工具的前身EXP如果想要完成同样的任务非常的国唯.我们以揖除局部表为例看一下这个选项带给我们的便利。如果在命令行中完成备份.特殊字符的转义需要特别注意(我这里使用的是1.inUX操作系统)。1 .EXPDP帮助中的描述信息Ora1.ogSeCDBZexpdpSexpdp-he1.pEXC1.UDEExc1.udespecific

2、objecttypes,e.g.EXC1.UDE=TAB1.E：EMP.2 .创立directory数据库对象并将读写权限授予SeC用户sysora1.Ogcreateorrep1.acedirectorydump.dirasVexpdp,；Directorycreated.sysora1.Oggrantread.writeondirectorydumpdirtosec；Grantsucceeded.3 .确认操作系筑信息OraIOgSeCDBexpdpSUnamC-a1.inuxsecDB2.6.18-128.e15#1SMPWedDec1711:41:38EST2008x8664x8664

3、x8664GNU/1.inux4 .在sec用户下创立几张表用于后埃的幽创立三张表T】、T2和T3.每张表中初始化一条数据secora10gsecora1.OgSeC侬ra1.gsecora1.OgseceOra1.ogsecOraIOgsectora1.0gcreateinsertcreateinsertcreateinsertcommit：tab1.et1.(xint)：intot1.va1.ues(1)；tab1.et2(xint)；intot2va1.ues(2)；tab1.et3(xint)；intot3va1.ues(3)；5 .为了与后面的比较，先全用户导出Ora1.OgQsec

4、DBZexpdpSrn-fsec.dnpsec.1.ogOra1.OgSOCUBZexpdpSexpdpsec/secdirectory=dump_dirdump1.ogExport：Re1ease10.2.0.3.0-61.bitProductiononMonday.08March.20109:59:25Copyriht(c)2003,2006.Orac1.e.A1.1rightsreserved.Connectedto：Orac1.eDotabnseIOgEnterpriseIiditionRe1.ease10.2.0.3.0-64bi1.ProductionWiththePartitio

5、nin.Orac1.e1.ube1.Security.O1.PandDataMiningScorinEngineoptionsStarting-SEC.$YS_EXPORTSeHEMAjM2sec,*Jireetory=iunp_dirdunp1.ogEstimateinprogressusingB1.0CKSIIR1.hOdProcessingobjecttypeSCHEM1.EXPORIVTAB1.E/TRB1.E_DATATota1.estimationusingB1.OCKSmethod：192KBProcessingobjecttypeSCHEMAProcessingobjectty

6、peSCHEMAProcessingobjecttypeSCHEM1.tProcessingobjecttypeSeIoAProcessingobjecttypeSCHEMAProcessingobjecttypeSCHEMA.ProcessingobjecttypeSCHEUAProcessingobjecttypeSCIIEiMEXPOR1.1.SEREXKWT/SYSTEMGRANTEXPORT/RO1.EGRANTEXPORTVDEFAU1.TRO1.E1.XK)RPRESQ1EMPROCACSCHEMAEXPORrrAB1.E/TRB1.EEXPORT”AB1.E/1NDEX/1ND

7、EXEXpoRr/TAB1.E/CONSTRAINT/CONSTRAINTProcessingobjecttypeSCHEMA.ProcessingobjecttypeSCHEMA.EXPOCTT三EINDEVSTTIST1CSIM)EXSTATISTICS.EXPORTT.B1.ECOMMENT.exportedrSECr.4.914KB1rows.exportedSEC。T24.914KB1rows.exported-SEC,.T3,Mastertab1.eSEC.SYSEXPORTSQ1.BIAO1.,successfu1.1.y4.914KBIoadedZun1.oaded1rou,s

8、Du111.)forSEC.SYSEXPORT.SCI三AO1.is：expdpsec.dmJobSEC*.SYSEXPORTSCHEMAO1.rsuccessfu1.Iycomp1.etedat09:59:326 .排除T1.表进行备份OraIogSCeDBZexpdpSrm-fsec.1.mpsec.1.ogora1.OgsecDB/expdp$expdpsec/secdirectory=dumpdirdump1.ogEXC1.fDE=TAB1.EIN(,T1,)Export：Re1.ease10.2.0.3.0-64bitProductiononMonday.08March.201010

9、:02:03Copyright(c)2003,2005.Orac1.e.A1.1.rightsreserved.Connectedto：Orac1.eDatabaseIOgEnterpriseEditionRe1.ease10.2.0.3.0-61.bitProductionWiththePartitioning.Orac1.e1.abe1.Security,O1.APandDataMiningScoringBngineoptionsStiirtins*SEC*.*SYS_EXPORTSG1.EMAj)1:sec*directory-du11p,dirdu11p1.ogEXC1.U)E=TB1

10、.EINCT)*EstimateinprogressusingH1.oCKSmethod.EXPORTT.B1.ETAB1.E.DATProcessingobjecttypeSCHEMA.Tota1.estimationusingB1.OCKSiiethod：128KBProcessingobjecttypeSCHEMAEXpORT/1.SERProcessingobjecttypeSCHEUAEXPoR1.VSYSTEk1.GRANTProcessingobjecttypeSCHE呼EXPORT/RO1.EGRANTProcessingobjecttypSQ1.EMAEXPoRT/DEFAU

11、1.TROiJ-ProcessingobjecttypeSa1.EMAEXPOCT/PRESCWWPROCCTSCHEMAProcessingobjecttypeSaIEMAEXPORT/TAB1.E/TAB1.EProcessingobjecttypeSCHEMA1.XPORTT三.HINDEX/1NDEXProcessingobjecttypeSCIIDMEXpORTABuycONSTRAIKTAWSTRAINrProcessingobjecttypeSCIIEUVEXPORTT.B1.EINDEVSTTISTICSINDEXSTATISTICSProcessingobjecttypeSC

12、HEUAEXpoRTVTAB1.EtOMMEXT.exportedSECr.T2r4.914KB1rows.exported*SECr.*T3r4.914KB1rowsMastertab1.e*SECt.-SYS_EXP0RTSQIEMA_01successfu1.1.yIoadedZun1.oaded*重#*它*衾#*京#*食#*京#它*权*分#*#*它*衾#*#*#*食今*权*Du11pforsec.Sysexpor1.schemaOiis；exsec.1.mjJobrSECh.SYSJ-XPORTSCHEMA0successfu1.1.ycomp1.etedat10:02:10排除表TI

13、后T2和T3表被成功导出。7 .排除多张表进行备份以排除表TI和T2两张表为例进行演示Ora1.OgsecDBexpdp5rm-fsec.dmpsec.1.ogOraIogSCeDBZexpdpSCXpdPsec/secdir。Ctory=dump_dirdump1.ogexc1.1.de=tb1.e：,in(,.,T2,)Export：Re1ease10.2.0.3.0-64bitProductiononMonday,08March.201010:03：17Copyright(c)2003.2005.Orac1.e.A1.1.rightsreserved.Connectedto:Orac1.

14、eDatabaseIOgEntefpriseEditionRe1.ease10.2.0.3.0-64bitProductionWiththePartitioning.Orac1.e1.abe1.Security.O1.APandDataMiningScoringEngineoptionsStartingSEC.SYSEXPORTSCHEMA01r:sec,EstimateinprogressusingB1.OCKSmethod.ProcessingobjecttypeSCHEMAEXPORTTB1.ETB1.E!)TTota1.OStinm1.iOnusingB1.OCKSmethod:61KBProcessingobjectEypeSa1.EMA_EXPORi71SERProcessingobjecttypeSCHEMAEXPORTySYSTEHGRANTProcessingobjecttypeSCHEMtEXP0R17R01.EGRANTProcessingobjecttypeSCHE呼EXPORT/DEFAU1.TRO1.EProcessingobjecttype