数据泵导入导出详解

数据泵技术是Oracle Database 10g中的新技术，它比原来导入/导出(imp,exp)技术快15 -45倍速度的提高源于使用了并行技术来读写导出转储文件expdp使用使用EXPDP工具时，其转储文件只能被存放在DIRECTORY对象对应的OS目录中， 而不能直接指定转储文件所在的OS目录因此使用EXPDP工具时，，必须首先建立 DIRECTORY对象，并且需要为数据库用户授予使用DIRECTORY对象权限首先得建DIRECTORY：SQL> conn /as sysdbaSQL> CREATE OR REPLACE DIRECTORY dir_dump AS '/u01/backup/';SQL> GRANT read,write ON DIRECTORY dir_dump TO public;1）导出 scott 整个 schema--默认导出登陆账号的schema$ expdpscott/tiger@db_esuiteparfile=/orahome/expdp.parexpdp.par 内容：DIR ECTORY=dir_dumpDUMPFILE=scott_full.dmpLOGFILE=scott_full.log--其他账号登陆，在参数中指定schemas$ expdp system/oracle@db_esuiteparfile=/orahome/expdp.parexpdp.par 内容：DUMPFILE=scott_full.dmpLOGFILE=scott_full.logSCHEMAS=SCOTT2） 导出scott下的dept,emp表$ expdpscott/tiger@db_esuiteparfile=/orahome/expdp.parexpdp.par 内容：DIR ECTORY=dir_dumpDUMPFILE=scott.dmpLOGFILE=scott.logTABLES=DEPT,EMP3） 导出scott下除emp之外的表$ expdpscott/tiger@db_esuiteparfile=/orahome/expdp.parexpdp.par 内容：DIR ECTORY=dir_dumpDUMPFILE=scott.dmpLOGFILE=scott.logEXCLUDE=TABLE:"='EMP'"4） 导出scott下的存储过程$ expdpscott/tiger@db_esuiteparfile=/orahome/expdp.parexpdp.par 内容：DUMPFILE=scott.dmpLOGFILE=scott.logINCLUDE=PROCEDURE5） 导出scott下以'E'开头的表$ expdpscott/tiger@db_esuiteparfile=/orahome/expdp.parexpdp.par 内容：DIR ECTORY=dir_dumpDUMPFILE=scott.dmpLOGFILE=scott.logINCLUDE=TABLE:"LIKE 'E%'" /何以改成NOT LIKE,就导出不以E开头的表6） 带QUERY导出$ expdpscott/tiger@db_esuiteparfile=/orahome/expdp.parexpdp.par 内容：DIR ECTORY=dir_dumpDUMPFILE=scott.dmpLOGFILE=scott.logTABLES=EMP,DEPTQUERY=EMP:"whereempno>=8000”QUERY=DEPT:"wheredeptno>=10 and deptno<=40"注：处理这样带查询的多表导出，如果多表之间有外健关联，可能需要注意查询条件所 筛选的数据是否符合这样的外健约束，比如EMP中有一栏位是deptno,是关联dept中的 主键，如果"whereempno>=8000”中得出的deptno=50的话，那么，你的dept的条件”where deptno>=10 and deptno<=40"就不包含deptno=50的数据，那么在导入的时候就会出现错 误.expdp选项1. ATTACH该选项用于在客户会话与已存在导出作用之间建立关联.语法如下：ATTACH=[schema_name.]job_nameschema_name用于指定方案名,job_name用于指定导出作业名.注意，如果使用 ATTACH选项，在命令行除了连接字符串和ATTACH选项外，不能指定任何其他选项,示例如 下：expdpscott/tiger ATTACH=scott.export_job2. CONTENT该选项用于指定要导出的内容.默认值为ALL.语法如下：CONTENT={ALL | DATA_ONLY |METADATA_ONLY}当设置CONTENT为ALL时，将导出对象定义及其所有数据；为DATA_ONLY时，只导 出对象数据；为METADATA_ONLY时，只导出对象定义，示例如下：expdpscott/tiger DIRECTORY=dump DUMPFILE=a.dump CONTENT=METADATA_ONLY3. DIRECTORY指定转储文件和日志文件所在的目录.语法如下：DIR ECTORY=directory_objectdirectory_object用于指定目录对象名称.需要注意,目录对象是使用CREATE DIRECTORY语句建立的对象,而不是OS目录，示例如下：expdpscott/tiger DIRECTORY=dump DUMPFILE=a.dump建立目录:CREATE DIRECTORY dump as 'd:dump';查询创建了那些子目录：SELECT * FROM dba_directories;4. DUMPFILE用于指定转储文件的名称，默认名称为expdat.dmp.语法如下：DUMPFILE=[directory_object:]file_name[,・・・.]directory_object用于指定目录对象名,file_name用于指定转储文件名.需要注意,如果不 指定directory_object，导出工具会自动使用DIRECTORY选项指定的目录对象，示例如下：expdpscott/tiger DIRECTORY=dump1 DUMPFILE=dump2:a.dmp5. ESTIMATE指定估算被导出表所占用磁盘空间的方法.默认值是BLOCKS.语法如下：EXTIMATE={BLOCKS | STATISTICS}设置为BLOCKS时,oracle会按照目标对象所占用的数据块个数乘以数据块尺寸估算对 象占用的空间，设置为STATISTICS时,根据最近统计值估算对象占用空间，示例如下：expdpscott/tiger TABLES=emp ESTIMATE=STATISTICS DIRECTORY=dumpDUMPFILE=a.dump一般情况下，当用默认值(blocks)时，日志中估计的文件大小会比实际expdp出来的文 件大，用statistics时会跟实际大小差不多.6. EXTIMATE_ONLY指定是否只估算导出作业所占用的磁盘空间，默认值为N.语法如下：EXTIMATE_ONLY={Y | N}设置为Y时，导出作用只估算对象所占用的磁盘空间，而不会执行导出作业，为N时，不仅 估算对象所占用的磁盘空间，还会执行导出操作，示例如下：expdpscott/tiger ESTIMATE_ONLY=y NOLOGFILE=y7. EXCLUDE该选项用于指定执行操作时要排除的对象类型或相关对象.语法如下：EXCLUDE=object_type[:name_clause][，….]object_type用于指定要排除的对象类型,name_clause用于指定要排除的具体对 象EXCLUDE和INCLUDE不能同时使用，示例如下：expdpscott/tiger DIRECTORY=dump DUMPFILE=a.dup EXCLUDE=VIEW在EXPDP的帮助文件中，可以看到存在EXCLUDE和INCLUDE参数，这两个参数文 档中介绍的命令格式存在问题，正确用法是：EXCLUDE=OBJECT_TYPE[:name_clause][,...]INCLUDE=OBJECT_TYPE[:name_clause][,...]示例：Expdp schema=scottexclude=sequence,table:"in('EMP','DEPT')”impdp schema=scott include = function,package, procedure, table:"='EMP'"有了这些还不够，由于命令中包含了多个特殊字符，在不同的操作系统下需要通过转 义字符才能使上面的命令顺利执行，如：EXCLUDE=TABLE:"IN('BIGTALE')"8. FILESIZE指定导出文件的最大尺寸,默认为0(表示文件尺寸没有限制).9. FLASHBACK_SCN指定导出特定SCN时刻的表数据.语法如下：FLASHBACK_SCN=scn_valuescn_value用于标识 SCN 值.FLASHBACK_SCN和 FLASHBACK_TIME不能同时使用, 示例如下：expdpscott/tiger DIRECTORY=dump DUMPFILE=a.dmp FLASHBACK_SCN=35852310. FLASHBACK_TIME指定导出特定时间点的表数据.语法如下：FLASHBACK_TIME="TO_TIMESTAMP(time_value)"示例如下：expdpscott/tiger DIRECTORY=dump DUMPFILE=a.dmp FLASHBACK_TIME = "TO_TIMESTAMP('25-08-200414:35:00','DD-MM-YYYY HH24:MI:SS')"11. FULL指定数据库模式导出，默认为N.语法如下：FULL={Y | N}为Y时，标识执行数据库导出.12. HELP指定是否显示EXPDP命令行选项的帮助信息,默认为N.当设置为Y时，会显示导出选项 的帮助信息，示例如下：expdp help=y13. INCLUDE指定导出时要包含的对象类型及相关对象.语法如下:INCLUDE=object_type[:name_clause][，… ]示例如下：expdpscott/tiger DIRECTORY=dump DUMPFILE=a.dmp INCLUDE=trigger1.1.2 expdp 选项14. JOB_NAME指定要导出作用的名称,默认为SYS_XXX.语法如下：JOB_NAME=jobname_string示例如下：expdpscott/tiger DIRECTORY=dump DUMPFILE=a.dmp INCLUDE=triggerJOB_NAME=exp_trigger后面想临时停止expdp任务时可以按Ctrl+C组合键,退出当前交互模式,退出之后导出 操作不会停止，这不同于Oracle以前的EXP.以前的EXP,如果退出交互式模式，就会出错终 止导出任务.在Oracle10g中，由于EXPDP是数据库内部定义的任务,已经与客户端无关. 退出交互之后，会进入export的命令行模式，此时支持status等查看命令：Export> status如果想停止改任务，可以发出stop_job命令：Export>stop_job如果有命令行提示:”是否确实要停止此作业([Y]/N):"或"Are you sure you wish to stop this job ([yes]/no):”,回答应是yes或者no,回答是YES以后会退出当前的export界 面.接下来可以通过命令行再次连接到这个任务：expdp test/test@acf attach=expfull通过start_job命令重新启动导出:Export>start_jobExport> status15. LOGFILE指定导出日志文件文件的名称，默认名称为export.log.语法如下：LOGFILE=[directory_object:]file_namedirectory_object用于指定目录对象名称,file_name用于指定导出日志文件名.如果不指 定directory_object.导出作用会自动使用DIRECTORY的相应选项值，示例如下：expdpscott/tiger DI RECTORY=dump DUMPFILE=a.dmp logfile=a.log16. NETWORK_LINK指定数据库链名,如果要将远程数据库对象导出到本地例程的转储文件中，必须设置该 选项.expdp中使用连接字符串和network_link的区别：expdp属于服务端工具，而exp属于客户端工具,expdp生成的文件默认是存放在服务端 的，而exp生成的文件是存放在客户端.expdp username/password@connect_string //对于使用这种格式来说 ‘directory 使用源 数据库创建的，生成的文件存放在服务端。

如何将生成的文件放在目标数据库而不放在源数据库呢，在expdp中使用network_link. 比如在本机expdp远程服务器的数据库，先在本机创建到服务端的dblink，然后创建directory 及授权，然后expdp.a.创建到服务端的dblinkconn aa/aacccreate database link connect to identified by using '' ;//username和 password 是 server 端的b.创建 directoryconn / assysdbacreate or replace directory dir as '/home/oracle/dbbackup';grantread,write on directory dir to ;c.导出expdpusername2/password2 directory=dirnetwork_link=link_name ... 〃这里的 username2用创建dblink的那个用户aa, directory也是目标数据库创建的比如在本机expdp远程服务器的数据库，先在本机创建到服务端的dblink,然后创建 directory 及授权，然后 expdp useranme2/password2.....如果想不生成dmp文件而直接导入一个数据库，原理和上面类似，直接使用impdp带 network_link，这样可以直接impdp，而绕过了 expdp的步骤impdpnetwork_link=tolink schemas=link remap_schema=link:link217. NOLOGFILE该选项用于指定禁止生成导出日志文件，默认值为N.18. PARALLEL指定执行导出操作的并行进程个数，默认值为119. PARFILE指定导出参数文件的名称.语法如下：PARFILE=[directory_path:]file_name20. QUERY用于指定过滤导出数据的where条件.语法如下：QUERY=[schema.][table_name:]query_clauseschema用于指定方案名,table_name用于指定表名,query_clause用于指定条件限制 子句.QUERY 选项不能与 CONNECT = METADATA_ONLY, EXTIMATE_ONLY, TRANSPORT_TABLESPACES等选项同时使用，示例如下：expdpscott/tiger directory=dump dumpfiel=a.dmp Tables=empquery='WHERE deptno=20'21. SCHEMAS该方案用于指定执行方案模式导出，默认为当前用户方案.22. STATUS指定显示导出作用进程的详细状态，默认值为0.23. TABLES指定表模式导出.语法如下：TABLES=[schema_name.]table_name[:partition_name][，…]schema_name用于指定方案名,table_name用于指定导出的表名,partition_name用于 指定要导出的分区名.24. TABLESPACES指定要导出表空间列表.25. TRANSPORT_FULL_CHECK该选项用于指定被搬移表空间和未搬移表空间关联关系的检查方式默认为N.当设置为Y时，导出作用会检查表空间直接的完整关联关系,如果表所在表空间或其索引 所在的表空间只有一个表空间被搬移，将显示错误信息.当设置为N时，导出作用只检查单端依赖，如果搬移索引所在表空间，但未搬移表所在表 空间，将显示出错信息，如果搬移表所在表空间，未搬移索引所在表空间，则不会显示错误信息.26. TRANSPORT_TABLESPACES指定执行表空间模式导出.27. VERSION指定被导出对象的数据库版本，默认值为COMPATIBLE.语法如下：VERSION={COMPAT IBLE | LATEST |version_string}为COMPATIBLE时，会根据初始化参数COMPATIBLE生成对象元数据;为LATEST时, 会根据数据库的实际版本生成对象元数据.version_string用于指定数据库版本字符串.使用IMPDPIMPDP命令行选项与EXPDP有很多相同的，不同的有：1、 REMAP_DATAFILE该选项用于将源数据文件名转变为目标数据文件名，在不同平台之间搬移表空间时可能 需要该选项.REMAP_DATAFIEL=source_datafie:target_datafile2、 REMAP_SCHEMA该选项用于将源方案的所有对象装载到目标方案中.REMAP_SCHEMA=source_schema:target_schema3、 REMAP_TABLESPACE将源表空间的所有对象导入到目标表空间中REMAP_TABLESPACE=source_tablespace:target_tablespace4、 REUSE_DATAFILES该选项指定建立表空间时是否覆盖已存在的数据文件.默认为NREUSE_DATAFIELS={Y | N}5、 SKIP_UNUSABLE_INDEXES指定导入是是否跳过不可使用的索引，默认为N6、 SQLFILE指定将导入要指定的索引DDL操作写入到SQL脚本中SQLFILE=[directory_object:]file_nameImpdpscott/tiger DIRECTORY=dump DUMPFILE=tab.dmp SQLFILE=a.sql7、 STREAMS_CONFIGURATION指定是否导入流元数据(Stream Matadata)，默认值为Y.8、 TABLE_EXISTS_ACTION该选项用于指定当表已经存在时导入作业要执行的操作,默认为SKIPTABBLE_EXISTS_ACTION={SKIP | APPEND |TRUNCATE | FRPLACE }当设置该选项为SKIP时，导入作业会跳过已存在表处理下一个对象;当设置为APPEND 时，会追加数据，为TRUNCATE时，导入作业会截断表，然后为其追加新数据;当设置为 REPLACE时，导入作业会删除已存在表，重建表病追加数据，注意,TRUNCATE选项不适用与 簇表和NETWORK_LINK选项9、 TRANSFORM该选项用于指定是否修改建立对象的DDL语句TRANSFORM=transform_name:value[bject_type]Transform_name用于指定转换名，其中SEGMENT_ATTRIBUTES用于标识段属性(物 理属性，存储属性,表空间，日志等信息),STORAGE用于标识段存储属性,VALUE用于指定是 否包含段属性或段存储属性,obje ct_type用于指定对象类型.Impdpscott/tigerdirectory=dump dumpfile=tab.dmp Transform = segment_attributes:n:table10、 TRANSPORT_DATAFILES该选项用于指定搬移空间时要被导入到目标数据库的数据文件TRANSPORT_DATAFILE=datafile_nameDatafile_name用于指定被复制到目标数据库的数据文件Impdpsystem/manager DIRECTORY=dump DUMPFILE=tts.dmpTRANSPORT_DATAFILES = '/user01/data/tbs1.f'调用IMPDP1、 导入表Impdpscott/tiger DIRECTORY=dump_dir DUMPFILE=tab.dmpTABLES=dept,empImpdp system/manage DI RECTORY=dump_dir DUMPFILE=tab.dmpTABLES=scott.dept,scott.emp REMAP_SCHEMA=SCOTT:SYSTEM第一种方法表示将DEPT和EMP表导入到SCOTT方案中，第二种方法表示将DEPT 和EMP表导入的SYSTEM注意，如果要将表导入到其他方案中，必须指定REMAPSCHEMA选项.2、 导入方案Impdpscott/tiger DIRECTORY=dump_dir DUMPFILE=schema.dmpSCHEMAS=scottImpdp system/manager DIRECTORY=dump_dirDUMPFILE=schema.dmpSCHEMAS=scott REMAP_SCHEMA=scott:system3、 导入表空间Impdpsystem/manager DIRECTORY=dump_dir DUMPFILE=tablespace.dmp TABLESPACES=user014、 导入数据库Impdp system/manager DIRECTORY=dump_dir DUMPFILE=full.dmp FULL=y--赋予expdp,impdp的权限SQL> grant exp_full_database,imp_full_database to susan;附录资料：一、 创建逻辑目录，该命令不会在操作系统创建真正的目录，最好以system等管理员创建。

create directory dpdata1 as 'd:\test\dump';二、 查看管理理员目录（同时查看操作系统是否存在，因为Oracle并不关心该目录是否存 在，如果不存在，则出错）select * from dba_directories;三、 给scott用户赋予在指定目录的操作权限，最好以system等管理员赋予grantread,write on directory dpdatal to scott;四、 导出数据1） 按用户导expdpscott/tiger@orcl schemas=scottdumpfile=expdp.dmpDIRECTORY=dpdata1;2） 并行进程parallelexpdpscott/tiger@orcl directory=dpdata1 dumpfile=scott3.dmpparallel=40job_name=scott33） 按表名导expdpscott/tiger@orcl TABLES=emp,deptdumpfile=expdp.dmp DIR ECTORY=dpdata1;4） 按查询条件导expdpscott/tiger@orcl directory=dpdata1 dumpfile=expdp.dmpTables=emp query='WHERE deptno=20';5） 按表空间导expdp system/manager DIR ECTORY=dpdata1DUMPFILE=tablespace.dmpTABLESPACES=temp,example;6）导整个数据库expdp system/manager DIRECTORY=dpdata1 DUMPFILE=full.dmpFULL=y;五、还原数据1） 导到指定用户下impdpscott/tiger DIRECTORY=dpdata1 DUMPFILE=expdp.dmpSCHEMAS=scott;2） 改变表的ownerimpdp system/manager DIR ECTORY=dpdata1DUMPFILE=expdp.dmpTABLES=scott.dept REMAP_SCHEMA=scott:system;3） 导入表空间impdp system/manager DIR ECTORY=dpdata1DUMPFILE=tablespace.dmpTABLESPACES=example;4） 导入数据库impdb system/manager DIRECTORY=dump_dir DUMPFILE=full.dmpFULL=y;5） 追加数据impdp system/manager DIR ECTORY=dpdata1DUMPFILE=expdp.dmpSCHEMAS=system TABLE_EXISTS_ACTION=append;exp/imp与expdp/impdp的用法区别1 ：把用户usera的对象导到用户userb,用法区别在于fromuser=useratouser=userb ,remap_schema='usera':'usera'。

例如 imp system/passwdfromuser=useratouser=userbfile=/oracle/exp.dmp log=/oracle/exp.log; impdp system/passwddirectory=expdpdumpfile=expdp.dmpremap_schema='usera':'userb'logfile=/oracle/exp.log;2：更换表空间，用exp/imp的时候，要想更改表所在的表空间，需要手工去处理一下，如 alter table xxx move tablespace_new 之类的操作用 impdp 只要用 remap_tablespace='tabspace_old':'tablespace_new'3:当指定一些表的时候，使用 exp/imp 时，tables 的用法是 tables=（'table1','table2','table3'）expdp/impdp 的用法是 tables='table1','table2','table3'4:是否要导出数据行exp （ ROWS=Y导出数据行，ROWS=N不导出数据行）expdp content（ALL:对象+导出数据行，DATA_ONLY：只导出对象，METADATA_ONLY： 只导出数据的记录）Oracle数据泵导入导出案例2013-11-06 0 个评论 作者：laizhenhai88收藏 ，工-我要投稿Oracle数据泵导入导出案例Oracle数据库导入导出工具，可以使用exp/imp，但这是比较早期的工具。

本文主 要介绍数据泵expdp/impdp工具的使用建立数据泵目录使用数据泵需要先建directorycreate directory dump_scott as'/home/oracle/dump/scott'查看建立的目录Select * from dba_directories赋权Grant read,write on directory dump_scotttoscott导出案例1，按表导出expdpscott/tiger directory=dump_scottdumpfile=tab.dmp logfile=scott.log tables=dept,emp导出案例2,按用户导出expdpscott/tiger directory=dump_scottdumpfile=dumpscott.dmp schemas=scott导出案例3,全库导出，且并行导出expdpscott/tiger directory=dump_scottdumpfile=full.dmp parallel=4 full=y导入案例1，按表导入，从scott到scott2impdp scott2/tiger directory=dump_scottdumpfile=tab.dmptables=scott.dept,scott.empremap_schema=scott:scott2导入案例2，按用户导入，从scott到scott2impdpscott/tigerdirectory=dump_scottdumpfile=schema.dmpremap_schema=scott:scott2导入案例3,全库导入impdpscott/tiger directory=dump_scottdumpfile=full.dmp full=y导入案例4,无落地文件的用户拷贝，需要建立db linkimpdpscott/tigerdirectory=dump_scottnetwork_link=remote_linkremap_schema=scott:scott21、首先建立目录: create directory目录名称as '数据库服务器上的一个目录'，如: create directory别名as 'd:\服务器目录名'；将导入或导出的文件放在这个目录下2、导出及导入以SID=orcl，导出dmp的账号为test，导入dmp的账号为test为例。

若将数据从sfz中导出：expdp test/test@orcl directory=^名 dumpfile=导出文件名导入到test中：impdp test/test@orcl directory=^J名 dumpfile=导出文件名.dmp导入到处用户名不一样时，做个映射•，一样时，不用写remap_schema=test:test1。