[原创]Oracle数据导入导出(另ep和epdp对比)

1、本文包含 exp/imp,expdp/impdp 的使用说明和常用参数详解另外包括一个有趣的测试使用说明一、 oracle 数据库 expimpexpdpimpdp1 exp 数据导出1.1 exp 关键字说明关键字说明(默认值)userid 用户名 / 口令buffer 数据缓冲区大小file 输出文件 (expdat.dmp)compress 导入到一个区 (y)grants 导出权限(y)indexes 导出索引(y)direct 直接路径(n)- 直接导出速度较快log 屏幕输出的日志文件rows 导出数据行(y)consistent 交叉表的一致性(n)full 导出整个文件(n)o

2、wner 所有者用户名列表tables 表名列表recordlengthio 记录的长度inctype 增量导出类型record 跟踪增量导出 (y)triggers 导出触发器(y)statistics 分析对象(estimate)parfile 参数文件名constraints 导出的约束条件(y)object_consistent 只在对象导出期间设置为只读的事务处理(n)feedback 每 x 行显示进度 (0)filesize 每个转储文件的最大大小flashback_scn 用于将会话快照设置回以前状态的 scnflashback_time 用于获取最接近指定时间的 scn 的时

3、间query 用于导出表的子集的 select 子句resumable 遇到与空格相关的错误时挂起(n)resumable_name 用于标识可恢复语句的文本字符串resumable_timeoutresumable 的等待时间tts_full_check 对 tts 执行完整或部分相关性检查tablespaces 要导出的表空间列表transport_tablespace 导出可传输的表空间元数据(n)template 调用 ias 模式导出的模板名1.2 常用的 exp 关键字举例1 、 full 用于导出整个数据库，在rows=n 一起使用，导出整个数据库的结构。如： expuserid

4、=gwm/gwmfile=/test.dmplog=test.logfull=yrows=ndirect=y2 、 owner 和 tables ，用于定义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 、

5、 file 和 log 用于指定备份的 dmp 名称和 log 名称5 、 compress 不压缩导出数据的内容，默认y6 、 filesize 若导出的数据文件大，应该用该参数，限制文件大小不要超过2g如： expuserid=gwm/gwmfile=/test1,test2,test3,test4,test5filesize=2glog=test.log这样将创建test1.dmp,test2.dmp 等，每个文件大小为 2g 。1.3 exp 导出实例- - 数据库全备-expsystem/mangerlinkfile=e:file_name.dmpfull=ydirect=ylog=

6、e:log_name.log- - 可选择的备份数据库一些用户 -expsystem/mangerlinkfile=e:file_name.dmpowner=(username1,username2)direct=ylog=e:log_name.log- - 备份一个用户的数据-expusername/passwordlinkfile=e:file_name.dmpdirect=ylog=e:log_name.log2 imp 数据导入imp 关键字说明关键字说明 (默认值)关键字说明(默认值)userid 用户名 / 口令 full 导入整个文件(n)buffer 数据缓冲区大小 fromu

7、ser 所有者用户名列表file 输入文件 (expdat.dmp)touser 用户名列表show 只列出文件内容 (n)tables 表名列表ignore 忽略创建错误(n)recordlengthio 记录的长度grants 导入权限(y)inctype 增量导入类型indexes 导入索引(y)commit 提交数组插入(n)rows 导入数据行(y)parfile 参数文件名log 屏幕输出的日志文件constraints 导入限制 (y)destroy 覆盖表空间数据文件(n)indexfile 将表 / 索引信息写入指定的文件skip_unusable_indexes 跳过不可用

8、索引的维护 (n)feedback 每 x 行显示进度 (0)toid_novalidate 跳过指定类型id 的验证filesize 每个转储文件的最大大小statistics 始终导入预计算的统计信息resumable 在遇到有关空间的错误时挂起(n)resumable_name 用来标识可恢复语句的文本字符串resumable_timeoutresumable 的等待时间compile 编译过程 ,程序包和函数(y)streams_configuration 导入流的一般元数据 (y)streams_instantiation 导入流实例化元数据(n)下列关键字仅用于可传输的表空间tra

9、nsport_tablespace 导入可传输的表空间元数据(n)tablespaces 将要传输到数据库的表空间datafiles 将要传输到数据库的数据文件tts_owners 拥有可传输表空间集中数据的用户3 expdp 数据泵导出3.1 expdp 命令行选项1.attach该选项用于在客户会话与已存在导出作用之间建立关联 .语法如下attach=schema_name.job_nameschema_name 用于指定方案名 ,job_name 用于指定导出作业名.注意 ,如果使用 attach选项 ,在命令行除了连接字符串和attach 选项外,不能指定任何其他选项,示例如下:exp

10、dpscott/tigerattach=scott.export_job2.content该选项用于指定要导出的内容.默认值为allcontent=all|data_only|metadata_only当设置 content 为 all 时,将导出对象定义及其所有数据.为 data_only 时,只导出对象数据 ,为 metadata_only 时,只导出对象定义expdpscott/tigerdirectory=dumpdumpfile=a.dumpcontent=metadata_only3.directory指定转储文件和日志文件所在的目录directory=directory_obje

11、ctdirectory_object 用于指定目录对象名称.需要注意,目录对象是使用createdirectory语句建立的对象,而不是 os 目录expdpscott/tigerdirectory=dumpdumpfile=a.dump4.dumpfile用于指定转储文件的名称,默认名称为expdat.dmp.dumpfile=directory_object:file_name,directory_object 用于指定目录对象名 ,file_name 用于指定转储文件名.需要注意,如果不指定 directory_object, 导出工具会自动使用 directory 选项指定的目录对象e

12、xpdpscott/tigerdirectory=dump1dumpfile=dump2:a.dmp5.estimate指定估算被导出表所占用磁盘空间分方法.默认值是blocksestimate=blocks|statistics设置为blocks时,oracle会按照目标对象所占用的数据块个数乘以数据块尺寸估算对象占用的空间,设置为statistics 时,根据最近统计值估算对象占用空间expdpscott/tigertables=empestimate=statisticsdirectory=dumpdumpfile=a.dump6.estimate_only指定是否只估算导出作业所占用的

13、磁盘空间,默认值为nextimate_only=y|n设置为 y 时,导出作用只估算对象所占用的磁盘空间,而不会执行导出作业,为 n 时 ,不仅估算对象所占用的磁盘空间,还会执行导出操作.expdpscott/tigerestimate_only=ynologfile=y7 .exclude（ 具体见 2 、 exclude 导出用户中指定类型的指定对象）该选项用于指定执行操作时释放要排除对象类型或相关对象exclude=object_type:name_clause,.object_type 用 于 指 定 要 排 除 的 对 象 类 型 ,name_clause 用 于 指 定 要 排 除

14、 的 具 体 对象 .exclude 和 include 不能同时使用expdpscott/tigerdirectory=dumpdumpfile=a.dupexclude=view8 .filesize指定导出文件的最大尺寸,默认为0,（表示文件尺寸没有限制 ）9 .flashback_scn指定导出特定scn 时刻的表数据flashback_scn=scn_valuescn_value 用于标识 scn 值.flashback_scn 和 flashback_time 不能同时使用expdpscott/tigerdirectory=dumpdumpfile=a.dmpflashback_s

15、cn=35852310 .flashback_time指定导出特定时间点的表数据flashback_time= ” to_timestamp(time_value)”expdpscott/tigerdirectory=dumpdumpfile=a.dmpflashback_time=“ to_timestamp( 25-08- 200414:35:00 , -mdmd- yyyyhh24:mi:ss ) ”11 .full指定数据库模式导出,默认为nfull=y|n为 y 时 ,标识执行数据库导出 .12 .help指定是否显示expdp 命令行选项的帮助信息,默认为n当设置为 y 时 ,会显

16、示导出选项的帮助信息.expdphelp=y13.include (具体见 1、 include 导出用户中指定类型的指定对象)指定导出时要包含的对象类型及相关对象includ e=object_type:name_clause,14.job_name指定要导出作用的名称,默认为 sys_xxxjob_name=jobname_stringselect*fromdba_datapump_jobs;-查看存在的 job15.logfile指定导出日志文件文件的名称,默认名称为export.loglogfile=directory_object:file_namedirectory_object

17、用于指定目录对象名称,file_name 用于指定导出日志文件名 .如果不指定directory_object. 导出作用会自动使用 directory 的相应选项值.expdpscott/tigerdirectory=dumpdumpfile=a.dmplogfile=a.log16.network_link指定数据库链名 ,如果要将远程数据库对象导出到本地例程的转储文件中 ,必须设置该选项.如：expdpgwm/gwmdirectory=dir_dpnetwork_link=igisdbtables=p_street_areadumpfile=p_street_area.dmplogfil

18、e=p_street_area.logjob_name=my_jobigisdb 是目的数据库与源数据的链接名，dir_dp 是目的数据库上的目录而如果直接用使用连接字符串 (fgisdb),expdp 属于服务端工具， expdp 生成的文件默认是存放在服务端的17.nologfile该选项用于指定禁止生成导出日志文件,默认值为n.18.parallel指定执行导出操作的并行进程个数,默认值为1注：并行度设置不应该超过cpu 数的 2 倍，如果 cpu 为 2 个，可将 parallel 设为 2 ，在导入时速度比 parallel 为 1 要快而对于导出的文件，如果parallel 设为

19、2 ， 导出文件只有一个， 导出速度提高不多， 因为导出都是到同一个文件，会争抢资源。所以可以设置导出文件为两个,如下所示：expdpgwm/gwmdirectory=d_testdumpfile=gwmfile1.dp,gwmfile2.dpparallel=219.parfile指定导出参数文件的名称parfile=directory_pathfile_name20.query用于指定过滤导出数据的 where 条件query=schema.table_name:query_clauseschema 用于指定方案名 ,table_name 用于指定表名 ,query_clause 用于指定

20、条件限制子 句.query选项不能与connect=metadata_only,extimate_only,transport_tablespaces 等选项同 时使用 . expdpscott/tigerdirectory=dumpdumpfile=a.dmptables=empquery= wheredeptno=20 21.schemas 该方案用于指定执行方案模式导出,默认为当前用户方案.22.status 指定显示导出作用进程的详细状态,默认值为023.tables 指定表模式导出 tables=schema_name.table_name:partition_name,schema

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

22、会显示错误信息.26.transport_tablespaces 指定执行表空间模式导出27.version指定被导出对象的数据库版本,默认值为compatible.version=compatible|latest|version_string为 compatible 时,会根据初始化参数 compatible 生成对象元数据;为 latest 时,会根据数据库的实际版本生成对象元数据.version_string 用于指定数据库版本字符串 .调用expdp3.2 数据泵工具导出的步骤1 、创建 directorycreatedirectorydir_dpasd:oracledir_dp;2

23、 、授权grantread,writeondirectorydir_dptolttfm;- 查看目录及权限selectprivilege,directory_name,directory_pathfromuser_tab_privst,all_directoriesdwheret.table_name(+)=d.directory_nameorderby2,1;3 、执行导出expdplttfm/lttfmfgisdbschemas=lttfmdirectory=dir_dpdumpfile=expdp_test1.dmplogfile=expdp_test1.log;连接到 :oracled

24、atabase10genterpriseeditionreleasewiththepartitioning,olapanddataminingoptions启动 lttfm.sys_export_schema_01:lttfm/*fgisdbschory=dir_dpdumpfile=expdp_test1.dmplogfile=expdp_test1.log;*/3.3 其他使用说明1 、 directory=dir_dp 必须放在前面，如果将其放置最后，会提示ora-39002: 操作无效ora-39070: 无法打开日志文件。ora-39087: 目录名 data_pum

25、p_dir; 无效2 、在导出过程中， datadump 创建并使用了一个名为 sys_export_schema_01 的对象，此对象就是datadump 导出过程中所用的 job 名字，如果在执行这个命令时如果没有指定导出的 job 名字那么就会产生一个默认的 job 名字，如果在导出过程中指定 job名字就为以指定名字出现如下改成：expdplttfm/lttfmfgisdbschemas=lttfmdirectory=dir_dpdumpfile=expdp_test1.dmplogfile=expdp_test1.log,job_name=my_job1;3、导出语句后面不要有分号，

26、否则如上的导出语句中的 job 表名为 my_job1; ，而不是my_job1 。 因此导致 expdplttfm/lttfmattach=lttfm.my_job1执行该命令时一直提示找不到 job 表4 、创建的目录一定要在数据库所在的机器上。否则也是提示:ora-39002: 操作无效ora-39070: 无法打开日志文件。ora-39087: 目录名 data_pump_dir; 无效3.4 导出的其他相关命令使用1 ) ctrl+c 组合键：在执行过程中，可以按ctrl+c 组合键退出当前交互模式，退出之后，导出操作不会停止2 ) exportstatus-查看当前 job 的状态

27、及相关信息3 ) exportstop_job-暂停 job (暂停 job 后会退出 expor 模式)4 ） 重 新 进 入export模 式 下 ：c:documentsandsettingsadministratorexpdplttfm/lttfmattach=lttfm.my_job1-语句后面不带分号5 ） exportstart_job-打开暂停的 job （并未开始重新执行）6 ） exportcontinue_client-通过此命令重新启动lttfm.my_job:7 ） exportkill_job- 取消当前的 job 并释放相关客户会话（将job 删除同时删除dmp

28、文件）8 ） exportexit_client-通过此命令退出 export 模式（通过4 ）可再进入export 模式下）注：导出完成后job 自动卸载9 .5 数据泵导出的各种模式1 、按表模式导出：expdplttfm/lttfmfgisdbtables=lttfm.b$i_exch_info,lttfm.b$i_manhole_infodumpfile=expdp_test2.dmplogfile=expdp_test2.logdirectory=dir_dpjob_name=my_job2 、按查询条件导出：expdplttfm/lttfmfgisdbtables=lttfm.b$

29、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_exp/user_exporcl_65file=d:exp_01.dmplog=d:exp_01.logtables=(t_exp)export:release

30、.0-productionon 星期三 10 月 1216:11:072011copyright(c)1982,2005,oracle.allrightsreserved.连接到 :oracledatabase10genterpriseeditionrelease.0-productionwiththepartitioning,olapanddataminingoptions已导出 zhs16gbk 字符集和 al16utf16nchar 字符集服务器使用 al32utf8 字符集(可能的字符集转换)即将导出指定的表通过常规路径.正在导出表 t_exp导出了 1

31、0000007 行成功终止导出, 没有出现警告。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)network_link=to_exp_65export:release.0-productionon 星

32、期三 ,1210 月 ,201116:22:16copyright(c)2003,2005,oracle.allrightsreserved.连接到 :oracledatabase10genterpriseeditionrelease.0-productionwiththepartitioning,olapanddataminingoptions启动 db_exp.sys_export_table_01:db_exp/*orcl_67directory=dir_expdumpfile=expdp_01.dmplogfile=expdp_01.logtables=(t_exp)n

33、etwork_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_01*db_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:documentsandsettingsadmi