Oracle数据导入导出(另exp和expdp对比)

1、本文包含exp/imp,expdp/impdp的使用说明和常用参数详解另外包括一个有趣的测试一、Oracle数据库EXPIMPEXPDPIMPDP使用说明1.Exp数据导出1.1exp关键字说明关键字说明(默认值)USERID用户名/口令BUFFER数据缓冲区大小FILE输出文件(EXPDAT.DMP)COMPRESS导入到一个区(Y)GRANTS导出权限(Y)INDEXES导出索引(Y)DIRECT直接路径(N)-直接导出速度较快LOG屏幕输出的日志文件ROWS导出数据行(Y)CONSISTENT交叉表的一致性(N)FULL导出整个文件(N)OWNER所有者用户名列表TABLES表名列表RE

2、CORDLENGTHIO记录的长度INCTYPE增量导出类型RECORD跟踪增量导出(Y)TRIGGERS导出触发器(Y)STATISTICS分析对象(ESTIMATE)PARFILE参数文件名CONSTRAINTS导出的约束条件(Y)OBJECT_CONSISTENT只在对象导出期间设置为只读的事务处理(N)FEEDBACK每x行显示进度(0)FILESIZE每个转储文件的最大大小FLASHBACK_SCN用于将会话快照设置回以前状态的SCNFLASHBACK_TIME用于获取最接近指定时间的SCN的时间QUERY用于导出表的子集的select子句RESUMABLE遇到与空格相关的错误时挂起

3、(N)RESUMABLE_NAME用于标识可恢复语句的文本字符串RESUMABLE_TIMEOUTRESUMABLE的等待时间TTS_FULL_CHECK对TTS执行完整或部分相关性检查TABLESPACES要导出的表空间列表TRANSPORT_TABLESPACE导出可传输的表空间元数据(N)TEMPLATE调用iAS模式导出的模板名12.常用的exp关键字举例1、full用于导出整个数据库，在rows=n一起使用，导出整个数据库的结构。女口：expuserid=gwm/gwmfile=/test.dmplog=test.logfull=yrows=ndirect=y2、OWNER和TABL

4、ES，用于定义exp导出的对象如：expuserid=gwm/gwmfile=/test.dmplog=test.logowner=gwmtable=(table1,table2)3、buffer和feedback若导出数据较大，考虑使用这两个参数。如：expuserid=gwm/gwmfile=/test.dmplog=test.logfeedback=10000buffer=100000000tables=(table1,table2)4、file和log用于指定备份的dmp名称和log名称5、compress不压缩导出数据的内容，默认y6、filesize若导出的数据文件大，应该用该参数

5、，限制文件大小不要超过2g如：expuserid=gwm/gwmfile=/test1,test2,test3,test4,test5filesize=2Glog=test.log这样将创建test1.dmp,test2.dmp等，每个文件大小为2g。1.3exp导出实例-数据库全备-expsystem/mangerlinkfile=E:file_name.dmpfull=ydirect=ylog=E:log_name.log-可选择的备份数据库一些用户-expsystem/mangerlinkfile=E:file_name.dmpowner=(username1,username2)dir

6、ect=ylog=E:log_name.log-备份一个用户的数据-expusername/passwordlinkfile=E:file_name.dmpdirect=ylog=E:log_name.log2Imp数据导入imp关键字说明关键字说明(默认值)关键字说明(默认值)USERID用户名/口令FULL导入整个文件(N)BUFFER数据缓冲区大小FROMUSER所有者用户名列表FILE输入文件(EXPDAT.DMP)TOUSER用户名列表SHOW只列出文件内容(N)TABLES表名列表IGNORE忽略创建错误(N)RECORDLENGTHIO记录的长度GRANTS导入权限(Y)INCT

7、YPE增量导入类型INDEXES导入索引(Y)COMMIT提交数组插入(N)ROWS导入数据行(Y)PARFILE参数文件名LOG屏幕输出的日志文件CONSTRAINTS导入限制(Y)DESTROY覆盖表空间数据文件(N)INDEXFILE将表/索引信息写入指定的文件SKIP_UNUSABLE_INDEXES跳过不可用索引的维护(N)FEEDBACK每x行显示进度(0)TOID_NOVALIDATE跳过指定类型ID的验证FILESIZE每个转储文件的最大大小STATISTICS始终导入预计算的统计信息RESUMABLE在遇到有关空间的错误时挂起(N)RESUMABLE_NAME用来标识可恢复语

8、句的文本字符串RESUMABLE_TIMEOUTRESUMABLE的等待时间COMPILE编译过程,程序包和函数(Y)STREAMS_CONFIGURATION导入流的一般元数据(Y)STREAMS_INSTANTIATION导入流实例化元数据(N)下列关键字仅用于可传输的表空间TRANSPORT_TABLESPACE导入可传输的表空间元数据(N)TABLESPACES将要传输到数据库的表空间DATAFILES将要传输到数据库的数据文件TTS_OWNERS拥有可传输表空间集中数据的用户3Expdp数据泵导出3.1EXPDP命令行选项ATTACH该选项用于在客户会话与已存在导出作用之间建立关联.

9、语法如下ATTACH=schema_name.job_nameSchema_name用于指定方案名,job_name用于指定导出作业名.注意，如果使用ATTACH选项,在命令行除了连接字符串和ATTACH选项外，不能指定任何其他选项，示例如下：Expdpscott/tigerATTACH=scott.export_jobCONTENT该选项用于指定要导出的内容默认值为ALLCONTENT=ALL|DATA_ONLY|METADATA_ONLY当设置CONTENT为ALL时，将导出对象定义及其所有数据.为DATA_ONLY时，只导出对象数据,为METADATA_ONLY时，只导出对象定义Expd

10、pscott/tigerDIRECTORY=dumpDUMPFILE=a.dumpCONTENT=METADATA_ONLYDIRECTORY指定转储文件和日志文件所在的目录DIRECTORY=directory_objectDirectory_object用于指定目录对象名称.需要注意，目录对象是使用CREATEDIRECTORY语句建立的对象,而不是OS目录Expdpscott/tigerDIRECTORY=dumpDUMPFILE=a.dumpDUMPFILE用于指定转储文件的名称，默认名称为expdat.dmpDUMPFILE=directory_object:file_name,.D

11、irectory_object用于指定目录对象名,file_name用于指定转储文件名.需要注意，如果不指定directory_object,导出工具会自动使用DIRECTORY选项指定的目录对象Expdpscott/tigerDIRECTORY=dump1DUMPFILE=dump2:a.dmpESTIMATE指定估算被导出表所占用磁盘空间分方法默认值是BLOCKSESTIMATE=BLOCKS|STATISTICS设置为BLOCKS时,oracle会按照目标对象所占用的数据块个数乘以数据块尺寸估算对象占用的空间，设置为STATISTICS时,根据最近统计值估算对象占用空间Expdpscot

12、t/tigerTABLES=empESTIMATE=STATISTICSDIRECTORY=dumpDUMPFILE=a.dumpESTIMATE_ONLY指定是否只估算导出作业所占用的磁盘空间，默认值为NEXTIMATE_ONLY=Y|N设置为Y时,导出作用只估算对象所占用的磁盘空间,而不会执行导出作业,为N时,不仅估算对象所占用的磁盘空间,还会执行导出操作.Expdpscott/tigerESTIMATE_ONLY=yNOLOGFILE=yEXCLUDE（具体见2、Exclude导出用户中指定类型的指定对象）该选项用于指定执行操作时释放要排除对象类型或相关对象EXCLUDE=object_

13、type:name_clause,.Object_type用于指定要排除的对象类型,name_clause用于指定要排除的具体对象.EXCLUDE和INCLUDE不能同时使用Expdpscott/tigerDIRECTORY=dumpDUMPFILE=a.dupEXCLUDE=VIEWFILESIZE指定导出文件的最大尺寸,默认为0,（表示文件尺寸没有限制）FLASHBACK_SCN指定导出特定SCN时刻的表数据FLASHBACK_SCN=scn_valueScn_value用于标识SCN值.FLASHBACK_SCN和FLASHBACK_TIME不能同时使用Expdpscott/tigerD

14、IRECTORY=dumpDUMPFILE=a.dmpFLASHBACK_SCN=358523FLASHBACK_TIME指定导出特定时间点的表数据FLASHBACK_TIME=”TO_TIMESTAMP(time_value)”Expdpscott/tigerDIRECTORY=dumpDUMPFILE=a.dmpFLASHBACK_TIME=“TO_TIMESTAMP(-0285-200414:35:00,-DMDM-YYYYHH24:MI:SS)”FULL指定数据库模式导出，默认为NFULL=Y|N为Y时，标识执行数据库导出.HELP指定是否显示EXPDP命令行选项的帮助信息,默认为N当

15、设置为Y时，会显示导出选项的帮助信息.Expdphelp=yINCLUDE(具体见1、Include导出用户中指定类型的指定对象)指定导出时要包含的对象类型及相关对象INCLUDE=object_type*:name_clause+*,+JOB_NAME指定要导出作用的名称,默认为SYS_XXXJOB_NAME=jobname_stringSELECT*FROMDBA_DATAPUMP_JOBS;-查看存在的jobLOGFILE指定导出日志文件文件的名称,默认名称为export.logLOGFILE=directory_object:file_nameDirectory_object用于指定目

16、录对象名称,file_name用于指定导出日志文件名.如果不指定directory_object.导出作用会自动使用DIRECTORY的相应选项值.Expdpscott/tigerDIRECTORY=dumpDUMPFILE=a.dmplogfile=a.logNETWORK_LINK指定数据库链名,如果要将远程数据库对象导出到本地例程的转储文件中,必须设置该选项.如:expdpgwm/gwmdirectory=dir_dpNETWORK_LINK=igisdbtables=p_street_areadumpfile=p_street_area.dmplogfile=p_street_area

17、.logjob_name=my_jobigisdb是目的数据库与源数据的链接名，dir_dp是目的数据库上的目录而如果直接用使用连接字符串(fgisdb),expdp属于服务端工具，expdp生成的文件默认是存放在服务端的NOLOGFILE该选项用于指定禁止生成导出日志文件，默认值为N.PARALLEL指定执行导出操作的并行进程个数,默认值为1注：并行度设置不应该超过CPU数的2倍，如果cpu为2个，可将PARALLEL设为2，在导入时速度比PARALLEL为1要快而对于导出的文件，如果PARALLEL设为2，导出文件只有一个，导出速度提高不多，因为导出都是到同一个文件，会争抢资源。所以可以设

18、置导出文件为两个,如下所示：expdpgwm/gwmdirectory=d_testdumpfile=gwmfile1.dp,gwmfile2.dpparallel=2PARFILE指定导出参数文件的名称PARFILE=directory_pathfile_nameQUERY用于指定过滤导出数据的where条件QUERY=schema.table_name:query_clauseSchema用于指定方案名,table_name用于指定表名,query_clause用于指定条件限制子句.QUERY选项不能与CONNECTnMETADATAONLYEXTIMATEONLYJRANSPORTTAB

19、LESPACES等选项同时使用.Expdpscott/tigerdirectory=dumpdumpfile=a.dmpTables=empquery=WHEREdeptno=20SCHEMAS该方案用于指定执行方案模式导出,默认为当前用户方案.STATUS指定显示导出作用进程的详细状态,默认值为0TABLES指定表模式导出TABLES=schema_name.table_name:partition_name,Schema_name用于扌旨定方案名,table_name用于扌旨定导出的表名,partition_name用于指定要导出的分区名.TABLESPACES指定要导出表空间列表TRAN

20、SPORT_FULL_CHECK该选项用于指定被搬移表空间和未搬移表空间关联关系的检查方式,默认为N.当设置为Y时,导出作用会检查表空间直接的完整关联关系,如果表空间所在表空间或其索引所在的表空间只有一个表空间被搬移,将显示错误信息.当设置为N时,导出作用只检查单端依赖,如果搬移索引所在表空间,但未搬移表所在表空间,将显示出错信息,如果搬移表所在表空间,未搬移索引所在表空间,则不会显示错误信息.TRANSPORT_TABLESPACES指定执行表空间模式导出VERSION指定被导出对象的数据库版本，默认值为COMPATIBLE.VERSION=COMPATIBLE|LATEST|version

21、_string为COMPATIBLE时，会根据初始化参数COMPATIBLE生成对象元数据;为LATEST时，会根据数据库的实际版本生成对象元数据.version_string用于指定数据库版本字符串.调用EXPDP3.2数据泵工具导出的步骤1、创建DIRECTORYcreatedirectorydir_dpasD:oracledir_dp;2、授权Grantread,writeondirectorydir_dptolttfm;-查看目录及权限SELECTprivilege,directory_name,DIRECTORY_PATHFROMuser_tab_privst,all_directo

22、riesdWHEREt.table_name(+)=d.directory_nameORDERBY2,1;3、执行导出expdplttfm/lttfmfgisdbschemas=lttfmdirectory=dir_dpdumpfile=expdp_test1.dmplogfile=expdp_test1.log;连接到:OracleDatabase10gEnterpriseEditionRelease10.2.0.1WiththePartitioning,OLAPandDataMiningoptions启动LTTFM.SYS_EXPORT_SCHEMA_01:lttfm/*fgisdbsch

23、ory=dir_dpdumpfile=expdp_test1.dmplogfile=expdp_test1.log;*/3.3其他使用说明1、directory=dir_dp必须放在前面，如果将其放置最后，会提示ORA-39OO2:操作无效ORA-39070:无法打开日志文件。ORA-39O87:目录名DATA_PUMP_DIR;无效2、在导出过程中，DATADUMP创建并使用了一个名为SYS_EXPORT_SCHEMA_O1的对象，此对象就是DATADUMP导出过程中所用的JOB名字，如果在执行这个命令时如果没有指定导出的JOB名字那么就会产生一个默认的JOB名字，如果在导出过程中指定JOB

24、名字就为以指定名字出现如下改成：expdplttfm/lttfmfgisdbschemas=lttfmdirectory=dir_dpdumpfile=expdp_test1.dmplogfile=expdp_test1.log,job_name=my_job1;3、导出语句后面不要有分号，否则如上的导出语句中的job表名为my_job1;,而不是my_job1。因此导致expdplttfm/lttfmattach=lttfm.my_job1执行该命令时一直提示找不到job表4、创建的目录一定要在数据库所在的机器上。否则也是提示:ORA-39OO2:操作无效ORA-39O7O:无法打开日志文件

25、。ORA-39087:目录名DATA_PUMP_DIR;无效3.4导出的其他相关命令使用1）Ctrl+C组合键：在执行过程中，可以按Ctrl+C组合键退出当前交互模式，退出之后，导出操作不会停止2）Exportstatus-查看当前JOB的状态及相关信息3）Exportstop_job暂停JOB（暂停job后会退出expor模式）4）重新进入export模式下：C:DocumentsandSettingsAdministratorexpdplttfm/lttfmattach=lttfm.my_job1-语句后面不带分号5）Exportstart_job-打开暂停的JOB（并未开始重新执行）6）

26、Exportcontinue_client-通过此命令重新启动LTTFM.MY_JOB:7）Exportkill_job-取消当前的JOB并释放相关客户会话（将job删除同时删除dmp文件）8）Exportexit_client-通过此命令退出export模式（通过4）可再进入export模式下）注：导出完成后job自动卸载3.5数据泵导出的各种模式1、按表模式导出：expdplttfm/lttfmfgisdbtables=lttfm.b$i_exch_info,lttfm.b$i_manhole_infodumpfile=expdp_test2.dmplogfile=expdp_test2.

27、logdirectory=dir_dpjob_name=my_job2、按查询条件导出：expdplttfm/lttfmfgisdbtables=lttfm.b$i_exch_infodumpfile=expdp_test3.dmplogfile=expdp_test3.logdirectory=dir_dpjob_name=my_jobquery=whererownumprompt$P$G$T$GC:DocumentsandSettingsAdministrator16:10:55.37C:DocumentsandSettingsAdministrator16:11:05.92expuser

28、_exp/user_exporcl_65file=d:exp_01.dmplog=d:exp_01.logtables=(t_exp)Export:Release10.2.0.1.0-Productionon星期三10月1216:11:072011Copyright(c)1982,2005,Oracle.Allrightsreserved.连接到:OracleDatabase10gEnterpriseEditionRelease10.2.0.1.0-ProductionWiththePartitioning,OLAPandDataMiningoptions已导出ZHS16GBK字符集和AL16

29、UTF16NCHAR字符集服务器使用AL32UTF8字符集(可能的字符集转换)即将导出指定的表通过常规路径.正在导出表T_EXP导出了10000007行成功终止导出,没有出现警告。C:DocumentsandSettingsAdministrator16:17:51.04用时：6分钟46秒Expdp导出过程如下：C:DocumentsandSettingsAdministrator16:22:15.25expdpdb_exp/db_exporcl_67directory=DIR_EXPdumpfile=expdp_01.dmplogfile=expdp_01.logtables=(t_exp)

30、network_link=to_exp_65Export:Release10.2.0.1.0-Productionon星期三,1210月,201116:22:16Copyright(c)2003,2005,Oracle.Allrightsreserved.连接到:OracleDatabase10gEnterpriseEditionRelease10.2.0.1.0-ProductionWiththePartitioning,OLAPandDataMiningoptions启动DB_EXP.SYS_EXPORT_TABLE_01:db_exp/*orcl_67directory=DIR_EXPd

31、umpfile=expdp_01.dmplogfile=expdp_01.logtables=(t_exp)network_link=to_exp_65正在使用BLOCKS方法进行估计.处理对象类型TABLE_EXPORT/TABLE/TABLE_DATA使用BLOCKS方法的总估计:336MB处理对象类型TABLE_EXPORT/TABLE/TABLE.导出了USER_EXP.T_EXP285.8MB10000007行已成功加载/卸载了主表DB_EXP.SYS_EXPORT_TABLE_01DB_EXP.SYS_EXPORT_TABLE_01的转储文件集为:D:DIREXPEXPDP_01.DMP作业DB_EXP.SYS_EXPORT_TABLE_01已于16:23:35成功完成C:DocumentsandSettingsAdministrator16:23:36.46用时：1分钟21秒另:Expdp导入到远程数据库服务端所耗时间如下：C:DocumentsandSettingsAdministrator16:27:40.92expdpuser_exp