原创Oracle数据导入导出

1、Oracle数据库导入导出 使用说明本文包含eexp/iimp,eexpdpp/imppdp的使使用说明和和常用参数数详解另外包括一一个有趣的的测试一、Oraacle数数据库EXXPIMMPEXXPDPIMPDDP使用说说明1Expp数据导出出1.1eexp关键键字说明关键字 说说明 (默默认值) -USERIID 用户户名/口令令 BUFFEER 数据据缓冲区大大小 FILE 输出文件件 (EXXPDATT.DMPP) COMPRRESS 导入到一一个区 (Y) GRANTTS 导出出权限 (Y) INDEXXES 导导出索引 (Y) DIRECCT 直接接路径 (N) -直接导导出速度较较

2、快 LOG 屏屏幕输出的的日志文件件 ROWS 导出数据据行 (YY) CONSIISTENNT 交叉叉表的一致致性 (NN) FULL 导出整个个文件 (N)OWNERR 所有者者用户名列列表TABLEES 表名名列表RECORRDLENNGTH IO记录录的长度INCTYYPE 增增量导出类类型RECORRD 跟踪踪增量导出出 (Y)TRIGGGERS 导出触发发器 (YY)STATIISTICCS 分析析对象 (ESTIIMATEE)PARFIILE 参参数文件名名CONSTTRAINNTS 导导出的约束束条件 (Y)OBJECCT_COONSISSTENTT 只在对对象导出期期间设置为

3、为只读的事事务处理 (N)FEEDBBACK 每 x 行显示进进度 (00)FILESSIZE 每个转储储文件的最最大大小FLASHHBACKK_SCNN 用于将将会话快照照设置回以以前状态的的 SCNNFLASHHBACKK_TIMME 用于于获取最接接近指定时时间的 SSCN 的的时间QUERYY 用于导导出表的子子集的 sselecct 子句句RESUMMABLEE 遇到与与空格相关关的错误时时挂起 (N)RESUMMABLEE_NAMME 用于于标识可恢恢复语句的的文本字符符串RESUMMABLEE_TIMMEOUTT RESSUMABBLE 的的等待时间间TTS_FFULL_CHEC

4、CK 对 TTS 执行完整整或部分相相关性检查查TABLEESPACCES 要要导出的表表空间列表表TRANSSPORTT_TABBLESPPACE 导出可传传输的表空空间元数据据 (N)TEMPLLATE 调用 iiAS 模模式导出的的模板名1.2常常用的exxp关键字字举例1、fulll用于导导出整个数数据库，在在rowss=n一起起使用，导导出整个数数据库的结结构。如：expp useerid=gwm/gwm filee=/teest.ddmp llog=ttest.log fulll=y rrows=n diirectt=y2、OWNNER和TTABLEES，用于于定义exxp导出的的

5、对象如：expp useerid=gwm/gwm filee=/teest.ddmp llog=ttest.log owneer=gwwm taable=(tabble1,tablle2)3、bufffer和和feeddbackk 若导出出数据较大大，考虑使使用这两个个参数。如：expp useerid=gwm/gwm filee=/teest.ddmp llog=ttest.log feeddbackk=100000 bbuffeer=10000000000 tablles=(tablle1,ttablee2)4、fille和loog 用于于指定备份份的dmpp名称和llog名称称5、com

6、mpresss 不压压缩导出数数据的内容容，默认yy6、fillesizze 若导导出的数据据文件大，应应该用该参参数，限制制文件大小小不要超过过2g如：expp useerid=gwm/gwm filee=/teest1,testt2,teest3,testt4,teest5 fileesizee=2G loog=teest.llog 这样将创建建testt1.dmmp,teest2.dmp等等，每个文文件大小为为2g。1.3eexp导出出实例-数据库库全备-exp ssysteem/maangerrlinnk fiile=EE:fiile_nname.dmp fulll=y ddirecc

7、t=y log=E:llog_nname.log-可选择择的备份数数据库一些些用户-exp ssysteem/maangerrlinnk fiile=EE:fiile_nname.dmp owneer=(uusernname11,useernamme2) direect=yy logg=E:log_namee.logg-备份一一个用户的的数据-exp uusernname/passsworddlinnk fiile=EE:fiile_nname.dmp direect=yy logg=E:log_namee.logg2Impp数据导入入imp关键键字说明关键字 说说明 (默默认值) 关键字 说

8、明 (默认值)-USERIID 用户户名/口令令 FULLL 导入入整个文件件 (N)BUFFEER 数据据缓冲区大大小 FRROMUSSER 所所有者用户户名列表FILE 输入文件件 (EXXPDATT.DMPP) TOOUSERR 用户名名列表SHOW 只列出文文件内容 (N) TABLLES 表表名列表IGNORRE 忽略略创建错误误 (N) RECCORDLLENGTTH IOO 记录的的长度GRANTTS 导入入权限 (Y) IINCTYYPE 增增量导入类类型INDEXXES 导导入索引 (Y) COMMMIT 提提交数组插插入 (NN)ROWS 导入数据据行 (YY) PAARF

9、ILLE 参数数文件名LOG 屏屏幕输出的的日志文件件 CONNSTRAAINTSS 导入限限制 (YY)DESTRROY 覆覆盖表空间间数据文件件 (N)INDEXXFILEE 将表/索引信息息写入指定定的文件SKIP_UNUSSABLEE_INDDEXESS 跳过不不可用索引引的维护 (N)FEEDBBACK 每 x 行显示进进度 (00)TOID_NOVAALIDAATE 跳跳过指定类类型 IDD 的验证证FILESSIZE 每个转储储文件的最最大大小STATIISTICCS 始终终导入预计计算的统计计信息RESUMMABLEE 在遇到到有关空间间的错误时时挂起 (N)RESUMMABL

10、EE_NAMME 用来来标识可恢恢复语句的的文本字符符串RESUMMABLEE_TIMMEOUTT RESSUMABBLE 的的等待时间间COMPIILE 编编译过程, 程序包包和函数 (Y)STREAAMS_CCONFIIGURAATIONN 导入流流的一般元元数据 (Y)STREAAMS_IINSTAANTIAATIONN 导入流流实例化元元数据 (N)下列关键字字仅用于可可传输的表表空间TRANSSPORTT_TABBLESPPACE 导入可传传输的表空空间元数据据 (N)TABLEESPACCES 将将要传输到到数据库的的表空间DATAFFILESS 将要传传输到数据据库的数据据文件T

11、TS_OOWNERRS 拥有有可传输表表空间集中中数据的用用户3Exppdp数据据泵导出3.1EEXPDPP命令行选选项1. ATTTACHH该选项用于于在客户会会话与已存存在导出作作用之间建建立关联.语法如下下ATTACCH=sschemma_naame.job_nameeSchemma_naame用于于指定方案案名,joob_naame用于于指定导出出作业名.注意,如如果使用AATTACCH选项,在命令行行除了连接接字符串和和ATTAACH选项项外,不能能指定任何何其他选项项,示例如如下:Expdpp scoott/ttigerr ATTTACH=scottt.exxportt_jobb2

12、. COONTENNT该选项用于于指定要导导出的内容容.默认值值为ALLLCONTEENT=ALL | DAATA_OONLY | MEETADAATA_OONLY当设置COONTENNT为ALLL时,将将导出对象象定义及其其所有数据据.为DAATA_OONLY时时,只导出出对象数据据,为MEETADAATA_OONLY时时,只导出出对象定义义Expdpp scoott/ttigerr DIRRECTOORY=ddump DUMPPFILEE=a.ddump CONTTENT=METAADATAA_ONLLY3. DIIRECTTORY指定转储文文件和日志志文件所在在的目录DIRECCTORY

13、Y=dirrectoory_oobjecctDirecctoryy_objject用用于指定目目录对象名名称.需要要注意,目目录对象是是使用CRREATEE DIRRECTOORY语句句建立的对对象,而不不是OS目目录Expdpp scoott/ttigerr DIRRECTOORY=ddump DUMPPFILEE=a.ddump4. DUUMPFIILE用于指定转转储文件的的名称,默默认名称为为expddat.ddmpDUMPFFILE=dirrectoory_oobjecct:ffile_namee ,.Direcctoryy_objject用用于指定目目录对象名名,fille_naam

14、e用于于指定转储储文件名.需要注意意,如果不不指定diirecttory_objeect,导导出工具会会自动使用用DIREECTORRY选项指指定的目录录对象Expdpp scoott/ttigerr DIRRECTOORY=ddump11 DUMMPFILLE=duump2:a.dmmp5. ESSTIMAATE指定估算被被导出表所所占用磁盘盘空间分方方法.默认认值是BLLOCKSSESTIMMATE=BLOOCKS | STTATISSTICSS设置为BLLOCKSS时,orraclee会按照目目标对象所所占用的数数据块个数数乘以数据据块尺寸估估算对象占占用的空间间,设置为为STATTIS

15、TIICS时,根据最近近统计值估估算对象占占用空间Expdpp scoott/ttigerr TABBLES=emp ESTIIMATEE=STAATISTTICS DIREECTORRY=duump DDUMPFFILE=a.duump6. ESSTIMAATE_OONLY指定是否只只估算导出出作业所占占用的磁盘盘空间,默默认值为NNEXTIMMATE_ONLYY=Y | N设置为Y时时,导出作作用只估算算对象所占占用的磁盘盘空间,而而不会执行行导出作业业,为N时时,不仅估估算对象所所占用的磁磁盘空间,还会执行行导出操作作.Expdpp scoott/ttigerr ESTTIMATTE_O

16、NNLY=yy NOLLOGFIILE=yy7. EXXCLUDDE(具体体见2、EExcluude导出出用户中指指定类型的的指定对象象)该选项用于于指定执行行操作时释释放要排除除对象类型型或相关对对象EXCLUUDE=oobjecct_tyype:namee_claause ,.Objecct_tyype用于于指定要排排除的对象象类型,nname_clauuse用于于指定要排排除的具体体对象.EEXCLUUDE和IINCLUUDE不能能同时使用用Expdpp scoott/ttigerr DIRRECTOORY=ddump DUMPPFILEE=a.ddup EEXCLUUDE=VVIEW8

17、. FIILESIIZE指定导出文文件的最大大尺寸,默默认为0,(表示文文件尺寸没没有限制)9. FLLASHBBACK_SCN指定导出特特定SCNN时刻的表表数据FLASHHBACKK_SCNN=scnn_vallueScn_vvaluee用于标识识SCN值值.FLAASHBAACK_SSCN和FFLASHHBACKK_TIMME不能同同时使用Expdpp scoott/ttigerr DIRRECTOORY=ddump DUMPPFILEE=a.ddmp FFLASHHBACKK_SCNN=358852310. FFLASHHBACKK_TIMME指定导出特特定时间点点的表数据据FLASH

18、HBACKK_TIMME=”TTO_TIIMESTTAMP(timee_vallue)”Expdpp scoott/ttigerr DIRRECTOORY=ddump DUMPPFILEE=a.ddmp FFLASHHBACKK_TIMME=“TTO_TIIMESTTAMP(25-08-22004 14:335:000,DDD-MMM-YYYYY HHH24:MMI:SSS)”11. FFULL指定数据库库模式导出出,默认为为NFULL=Y | N为Y时,标标识执行数数据库导出出.12. HHELP指定是否显显示EXPPDP命令令行选项的的帮助信息息,默认为为N当设置为YY时,会显显示导出选选

19、项的帮助助信息.Expdpp hellp=y13. IINCLUUDE（具具体见1、Incllude导导出用户中中指定类型型的指定对对象）指定导出时时要包含的的对象类型型及相关对对象INCLUUDE = objject_typee:naame_cclausse , 14. JJOB_NNAME指定要导出出作用的名名称,默认认为SYSS_XXXXJOB_NNAME=jobnname_striingSELECCT * FROMM DBAA_DATTAPUMMP_JOOBS;-查看存存在的joob15. LLOGFIILE指定导出日日志文件文文件的名称称,默认名名称为exxportt.loggLOG

20、FIILE=direectorry_obbjectt:fiile_nnameDirecctoryy_objject用用于指定目目录对象名名称,fiile_nname用用于指定导导出日志文文件名.如如果不指定定direectorry_obbjectt.导出作作用会自动动使用DIIRECTTORY的的相应选项项值.Expdpp scoott/ttigerr DIRRECTOORY=ddump DUMPPFILEE=a.ddmp llogfiile=aa.logg16. NNETWOORK_LLINK指定数据库库链名,如如果要将远远程数据库库对象导出出到本地例例程的转储储文件中,必须设置置该选项.如

21、：exppdp ggwm/ggwm ddirecctoryy=dirr_dp NETWWORK_LINKK=igiisdb tablles=pp_strreet_areaa dummpfille =pp_strreet_areaa.dmpp loggfilee=p_sstreeet_arrea.llog jjob_nname=my_jjobigisddb是目的的数据库与与源数据的的链接名，dir_ddp是目的的数据库上上的目录而如果直接接用使用连连接字符串串(fggisdbb),exxpdp属属于服务端端工具，eexpdpp生成的文文件默认是是存放在服服务端的17. NNOLOGGFILEE该

22、选项用于于指定禁止止生成导出出日志文件件,默认值值为N.18. PPARALLLEL指定执行导导出操作的的并行进程程个数,默默认值为11注：并行度度设置不应应该超过CCPU数的的2倍，如如果cpuu为2个，可可将PARRALLEEL设为22，在导入入时速度比比PARAALLELL为1要快快而对于导出出的文件，如如果PARRALLEEL设为22，导出文文件只有一一个，导出出速度提高高不多，因因为导出都都是到同一一个文件，会会争抢资源源。所以可可以设置导导出文件为为两个,如如下所示：expdpp gwmm/gwmm dirrectoory=dd_tesst duumpfiile=ggwmfiile

23、1.dp,ggwmfiile2.dp pparalllel=219. PPARFIILE指定导出参参数文件的的名称PARFIILE=direectorry_paath filee_namme20. QQUERYY用于指定过过滤导出数数据的whhere条条件QUERYY=scchemaa. tablle_naame: queery_cclausseSchemma用于指指定方案名名,tabble_nname用用于指定表表名,quuery_clauuse用于于指定条件件限制子句句.QUEERY选项项不能与CCONNEECT=MMETADDATA_ONLYY,EXTTIMATTE_ONNLY,TTRA

24、NSSPORTT_TABBLESPPACESS等选项同同时使用.Expdpp scoott/ttigerr dirrectoory=ddump dumppfilee=a.ddmp TTablees=emmp quuery=WHEERE ddeptnno=20021. SSCHEMMAS该方案用于于指定执行行方案模式式导出,默默认为当前前用户方案案.22. SSTATUUS指定显示导导出作用进进程的详细细状态,默默认值为0023. TTABLEES指定表模式式导出TABLEES=sschemma_naame.tablle_naame:parttitioon_naame,Schemma_naame

25、用于于指定方案案名,taable_namee用于指定定导出的表表名,paartittion_namee用于指定定要导出的的分区名.24. TTABLEESPACCES指定要导出出表空间列列表25. TTRANSSPORTT_FULLL_CHHECK该选项用于于指定被搬搬移表空间间和未搬移移表空间关关联关系的的检查方式式,默认为为N.当设置为YY时,导出出作用会检检查表空间间直接的完完整关联关关系,如果果表空间所所在表空间间或其索引引所在的表表空间只有有一个表空空间被搬移移,将显示示错误信息息.当设置置为N时,导出作用用只检查单单端依赖,如果搬移移索引所在在表空间,但未搬移移表所在表表空间,将将

26、显示出错错信息,如如果搬移表表所在表空空间,未搬搬移索引所所在表空间间,则不会会显示错误误信息.26. TTRANSSPORTT_TABBLESPPACESS指定执行表表空间模式式导出27. VVERSIION指定被导出出对象的数数据库版本本,默认值值为COMMPATIIBLE.VERSIION=COMPPATIBBLE | LATTEST | veersioon_sttringg为COMPPATIBBLE时,会根据初初始化参数数COMPPATIBBLE生成成对象元数数据;为LLATESST时,会会根据数据据库的实际际版本生成成对象元数数据.veersioon_sttringg用于指定定数据库

27、版版本字符串串.调用EEXPDPP3.2数数据泵工具具导出的步步骤1、创建DDIRECCTORYYcreatte diirecttory dir_dp aas DD:orracleedirr_dp; 2、授权Grantt reaad,wrrite on ddirecctoryy dirr_dp to llttfmm;-查看目目录及权限限SELECCT prrivillege, dirrectoory_nname, DIRRECTOORY_PPATH FROMM useer_taab_prrivs t, aall_ddirecctoriies ddWHEREE t.ttablee_namme(+

28、) = dd.dirrectoory_nname ORDEER BYY 2, 1;3、执行导导出expdpp ltttfm/llttfmmfgiisdb scheemas=lttffm diirecttory=dir_dp ddumpffile =exppdp_ttest11.dmpp loggfilee=exppdp_ttest11.logg;连接到: Oraccle DDatabbase 10g Enteerpriise EEditiion RReleaase 110.2.0.1With the Parttitiooningg, OLLAP aand DData Miniing oopti

29、oons启动 LLTTFMM.SSYS_EEXPORRT_SCCHEMAA_01: ltttfm/*fgiisdb schory=ddir_ddp duumpfiile =expddp_teest1.dmp logffile=expddp_teest1.log; */3.3其其他使用说明1、dirrectoory=ddir_ddp必须放放在前面，如如果将其放放置最后，会会提示 OORA-3390022: 操作作无效ORA-3390700: 无法法打开日志志文件。ORA-3390877: 目录录名 DAATA_PPUMP_DIR; 无效2、在导出出过程中，DDATA DUMPP 创建并并使用了一

30、一个名为SSYS_EEXPORRT_SCCHEMAA_01的的对象，此此对象就是是DATAA DUMMP导出过过程中所用用的JOBB名字，如如果在执行行这个命令令时如果没没有指定导导出的JOOB名字那那么就会产产生一个默默认的JOOB名字，如如果在导出出过程中指指定JOBB名字就为为以指定名名字出现如下改成：expdpp ltttfm/llttfmmfgiisdb scheemas=lttffm diirecttory=dir_dp ddumpffile =exppdp_ttest11.dmpp loggfilee=exppdp_ttest11.logg,jobb_namme=myy_jobb

31、1;3、导出语语句后面不不要有分号号，否则如如上的导出出语句中的的job表表名为my_jjob1;，而不是是my_jjob1。因此导致致expddp ltttfm/lttffm atttachh=ltttfm.mmy_joob1执行行该命令时时一直提示示找不到jjob表4、创建的的目录一定定要在数据据库所在的的机器上。否则也是是提示:ORA-3390022: 操作作无效ORA-3390700: 无法法打开日志志文件。ORA-3390877: 目录录名 DAATA_PPUMP_DIR; 无效3.4导导出的其他他相关命令令使用1）Ctrrl+C组组合键：在在执行过程程中，可以以按Ctrrl+C组组

32、合键退出出当前交互互模式，退退出之后，导导出操作不不会停止2）Expport staatus -查看看当前JOOB的状态态及相关信信息3）Expport stoop_joob -暂停JOOB（暂停停job后后会退出eexporr模式）4）重新进进入expport模模式下：CC:Doocumeents and SetttingssAdmminisstrattoreexpdpp ltttfm/llttfmm atttach=lttffm.myy_jobb1 -语句后面面不带分号号5）Expport staart_jjob -打开暂暂停的JOOB（并未未开始重新新执行）6）Expport connt

33、inuue_cllientt -通通过此命令令重新启动动 LTTTFM.MYY_JOBB:7）Expport killl_joob -取消当前前的JOBB并释放相相关客户会会话（将jjob删除除同时删除除dmp文文件）8）Expport exiit_cllientt -通通过此命令令退出exxportt模式（通通过4）可可再进入eexporrt模式下下）注：导出完完成后joob自动卸卸载3.5数数据泵导出出的各种模模式1、按表模模式导出：expdpp ltttfm/llttfmmfgiisdb tablles=llttfmm.b$ii_excch_innfo,llttfmm.b$ii_mann

34、holee_inffo duumpfiile =expddp_teest2.dmp logffile=expddp_teest2.log direectorry=diir_dpp jobb_namme=myy_jobb2、按查询询条件导出出：expdpp ltttfm/llttfmmfgiisdb tablles=llttfmm.b$ii_excch_innfo ddumpffile =exppdp_ttest33.dmpp loggfilee=exppdp_ttest33.logg dirrectoory=ddir_ddp joob_naame=mmy_joob quuery=whhere

35、rownnumppromppt $PP$G$TT$GC:Doocumeents and SetttingssAdmminisstrattor116:100:55.37C:Doocumeents and SetttingssAdmminisstrattor116:111:05.92eexp uuser_exp/userr_expporccl_65 fille=d:expp_01.dmp log=d:eexp_001.loog taabless=(t_exp)Exporrt: RReleaase 110.2.0.1.0 - Prodductiion oon 星期期三 100月 12 16:111:

36、077 20111Copyrrightt (c) 19882, 22005, Oraacle. Alll riightss resserveed.连接到: Oraccle DDatabbase 10g Enteerpriise EEditiion RReleaase 110.2.0.1.0 - ProdductiionWith the Parttitiooningg, OLLAP aand DData Miniing ooptioons已导出 ZZHS166GBK 字符集和和 AL116UTFF16 NNCHARR 字符集集服务器使用用 AL332UTFF8 字符符集 (可能的的字符集转转换)

37、即将导出指指定的表通通过常规路路径. . 正正在导出表表 TT_EXPP导出了 1000000007 行行成功终止导导出, 没有出出现警告。C:Doocumeents and SetttingssAdmminisstrattor116:177:51.04用时：6分分钟46秒Expdpp导出过程程如下：C:Doocumeents and SetttingssAdmminisstrattor116:222:15.25eexpdpp db_exp/db_eexpoorcl_67direcctoryy=DIRR_EXPP dummpfille=exxpdp_01.ddmp llogfiile=eexp

38、dpp_01.log tablles=(t_exxp) nnetwork_llink=to_eexp_665Exporrt: RReleaase 110.2.0.1.0 - Prodductiion oon 星期期三, 112 100月, 20011 116:222:16Copyrrightt (c) 20003, 22005, Oraacle. Alll riightss resserveed.连接到: Oraccle DDatabbase 10g Enteerpriise EEditiion RReleaase 110.2.0.1.0 - ProdductiionWith the Part

39、titiooningg, OLLAP aand DData Miniing ooptioons启动 DDB_EXXP.SYS_EXPOORT_TTABLEE_01: ddb_exxp/*oorcl_67 ddirecctoryy=DIRR_EXPPdumpffile=expddp_011.dmpp loggfilee=exppdp_001.loog taabless=(t_exp) nettworkk_linnk=too_expp_65正在使用 BLOCCKS 方方法进行估估计.处理对象类类型 TAABLE_EXPOORT/TTABLEE/TABBLE_DDATA使用 BLLOCKSS 方法的

40、的总估计: 3366 MB处理对象类类型 TAABLE_EXPOORT/TTABLEE/TABBLE. . 导导出了 USERR_EXPP.TT_EXPP 2285.88 MB 1000000077 行已成功加载载/卸载了主主表 DDB_EXXP.SYS_EXPOORT_TTABLEE_01*DB_EXXP.SYYS_EXXPORTT_TABBLE_001 的转转储文件集集为: D:DIREEXPEEXPDPP_01.DMP作业 DDB_EXXP.SYS_EXPOORT_TTABLEE_01 已于 166:23:35 成成功完成C:Doocumeents and SetttingssAdmminisstrattor116:233:36.46用时：1分分钟21秒另：Exppdp导入入到远程数数据库服务务端 所耗时间间如下：C:Doocumeents and SetttingssAdmminisstrattor116:277:40.92eexpdpp u