oracle----【EXPDP】使用EXPDP工具的 EXCLUDE选项过滤掉不关心的数据.docx
【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.udespecificobjecttypes,e.g.EXC1.UDE=TAB1.E：EMP.2 .创立directory数据库对象并将读写权限授予SeC用户sysora1.Og>createorrep1.acedirectorydump.dirasVexpdp,；Directorycreated.sys®ora1.Og>grantread.writeondirectorydumpdirtosec；Grantsucceeded.3 .确认操作系筑信息OraIOgSeCDBexpdpSUnamC-a1.inuxsecDB2.6.18-128.e15#1SMPWedDec1711:41:38EST2008x8664x8664x8664GNU/1.inux4 .在sec用户下创立几张表用于后埃的幽创立三张表T】、T2和T3.每张表中初始化一条数据secora10g>secora1.Og>SeC侬>ra1.g>secora1.Og>seceOra1.og>secOraIOg>sectora1.0g>createinsertcreateinsertcreateinsertcommit：tab1.et1.(xint)：intot1.va1.ues(1)；tab1.et2(xint)；intot2va1.ues(2)；tab1.et3(xint)；intot3va1.ues(3)；5 .为了与后面的比较，先全用户导出Ora1.OgQsecDBZexpdpSrn-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.ProductionWiththePartitionin.Orac1.e1.ube1.Security.O1.PandDataMiningScorinEngineoptionsStarting-SEC".$YS_EXPORTSeHEMAjM2sec,*<Jireetory=<iunp_dirdunp1.ogEstimateinprogressusingB1.0CKSIIR1.hOdProcessingobjecttypeSCHEM1.EXPORIVTAB1.E/TRB1.E_DATATota1.estimationusingB1.OCKSmethod：192KBProcessingobjecttypeSCHEMAProcessingobjecttypeSCHEMAProcessingobjecttypeSCHEM1.tProcessingobjecttypeSeIoAProcessingobjecttypeSCHEMAProcessingobjecttypeSCHEMA.ProcessingobjecttypeSCHEUAProcessingobjecttypeSCIIEiMEXPOR1.1.SEREXKWT/SYSTEMGRANTEXPORT/RO1.EGRANTEXPORTVDEFAU1.TRO1.E1.XK)RPRESQ1EMPROCACSCHEMAEXPORrrAB1.E/TRB1.EEXPORT”AB1.E/1NDEX/1NDEXEXpoRr/TAB1.E/CONSTRAINT/CONSTRAINTProcessingobjecttypeSCHEMA.ProcessingobjecttypeSCHEMA.EXPOCTT三EINDEVSTTIST1CSIM)EXSTATISTICS.EXPORTT.B1.ECOMMENT.exportedrSECr."4.914KB1rows.exported"SEC。"T2"4.914KB1rows.exported-SEC,."T3,Mastertab1.e"SEC'."SYSEXPORTSQ1.BIAO1.,successfu1.1.y4.914KBIoadedZun1.oaded1rou,sDu111.)forSEC.SYSEXPORT.SCI三AO1.is：expdpsec.dmJob"SEC*."SYSEXPORTSCHEMAO1.rsuccessfu1.Iycomp1.etedat09:59:326 .排除T1.表进行备份OraIogSCeDBZexpdpSrm-fsec.<1.mpsec.1.ogora1.OgsecDB/expdp$expdpsec/secdirectory=dumpdirdump1.ogEXC1.fDE=TAB1.E'IN(,T1,)"Export：Re1.ease10.2.0.3.0-64bitProductiononMonday.08March.201010: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.EINCT)*EstimateinprogressusingH1.oCKSmethod.EXPORTT.B1.ETAB1.E.DATProcessingobjecttypeSCHEMA.Tota1.estimationusingB1.OCKSiiethod：128KBProcessingobjecttypeSCHEMAEXpORT/1.SERProcessingobjecttypeSCHEUAEXPoR1.VSYSTEk1.GRANTProcessingobjecttypeSCHE呼EXPORT/RO1.EGRANTProcessingobjecttypSQ1.EMAEXPoRT/DEFAU1.TROiJ-ProcessingobjecttypeSa1.EMAEXPOCT/PRESCWWPROCCTSCHEMAProcessingobjecttypeSaIEMAEXPORT/TAB1.E/TAB1.EProcessingobjecttypeSCHEMA1.XPORTT三.HINDEX/1NDEXProcessingobjecttypeSCIIDMEXpORT"ABuycONSTRAIKTAWSTRAINrProcessingobjecttypeSCIIEUVEXPORTT.B1.EINDEVSTTISTICSINDEXSTATISTICSProcessingobjecttypeSCHEUAEXpoRTVTAB1.EtOMMEXT.exported"SECr."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后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.eDatabaseIOgEntefpriseEditionRe1.ease10.2.0.3.0-64bitProductionWiththePartitioning.Orac1.e1.abe1.Security.O1.APandDataMiningScoringEngineoptionsStarting"SEC".'SYSEXPORTSCHEMA01r:sec<iirectory=dunpdirdunp1.ogexc1.ude=tab1.e:"incti,.T2,>>,EstimateinprogressusingB1.OCKSmethod.ProcessingobjecttypeSCHEMAEXPORTTB1.ETB1.E!)TTota1.OStinm1.iOnusingB1.OCKSmethod:61KBProcessingobjectEypeSa1.EMA_EXPORi71SERProcessingobjecttypeSCHEMAEXPORTySYSTEHGRANTProcessingobjecttypeSCHEMtEXP0R17R01.EGRANTProcessingobjecttypeSCHE呼EXPORT/DEFAU1.TRO1.EProcessingobjecttype
