expdp和impdp的用法.docx

Expdp和Impdp的用法 马飞 2016-7-18一、 概述Oracle官方对此的形容是：OracleDataPump technology enables Very High-Speed movement of data and metadata from one database to another.其中Very High-Speed是亮点。先说数据泵提供的主要特性（包括，但不限于）：1. 支持并行处理导入、导出任务2. 支持暂停和重启动导入、导出任务3. 支持通过Database Link的方式导出或导入远端数据库中的对象4. 支持在导入时通过Remap_schema、Remap_datafile、Remap_tablespace几个参数实现导入过程中自动修改对象属主、 数据文件或数据所在表空间。5. 导入/导出时提供了非常细粒度的对象控制。通过Include、Exclude两个参数，甚至可以详细制定是否包含或不包含某个对象。二、什么是Directory对象 Directory对象是Oracle10g版本提供的一个新功能。他是一个 指向，指向了操作系统中的一个路径。每个Directory都包含 Read,Write两个权限，可以通过Grant命令授权给指定的用户或角色。拥有读写权限的用户就可以读写该Directory对象指定的操作系统路 径下的文件。 无论在什么地方使用expdp，生成的文件最终页是在服务器上(Directory指定的位置)三、如何调用1. 命令行方式 最简单的调用，但是写的参数有限，建议使用参数文件的方式。2. 参数文件方式最常用的方式。通常需要先编写一个参数文件。指定导出时需要的各种参数。然后以如下方式调用。expdp user/pwd parfile=xxx.par这个xxx.par即是我们编辑的参数文件。注意，在这个命令行后面，同样可以再跟别的参数， 甚至是在par参数文件中指定过的参数。如果执行命令中附加 的参数与参数文件中的参数有重复，最终采用哪个参数，会以参数最后出现的位置而定。如：expdp user/pwd parfile=xxx.par logfile=a.log，如果在参数文件中也指定了logfile，这里会以命令行中的logfile为准；如：expdp user/pwd logfile=a.log parfile=xxx.par，而这个，则会以参数文件中的为准，因为parfile=xxx.par写在命令行的后面。3. 交互方式Data Pump导入导出任务支持停止，重启等状态操作。如用户执行导入或者导出任务，执行了一半时，使用Crtl+C中断了任务（或其他原因导致的中断），此时 任务并不是被取消，而是被转移到后台。可以再次使用expdp/impdp命令，附加attach参数的方式重新连接到中断的任务中，并选择后续的操作。 这就是交互方式。Warning : 什么是attach参数，每执行一个导入，或者导出，在命令的第一行，会有以下信息：Starting “BAM”.”SYS_EXPORT_SCHEMA_01: bam/* parfile=expdp_tbs.par，这个SYS_EXPORT_SCHEMA_01就是我们的attach参数。-bash-3.00$ expdp bam/bam parfile=expdp_tbs.parExport: Release .0 64bit Production on Friday, 13 August, 2010 16:35:18Copyright (c) 2003, 2007, Oracle. All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release .0 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting “BAM”.”SYS_EXPORT_SCHEMA_01: bam/* parfile=expdp_tbs.par如果想 使用交互方式，可以使用如：expdp attach SYS_EXPORT_SCHEMA_01 进入到交互模式四、操作模式1. 全库模式 导入或者导出整个数据库，对应impdp/expdp命令中的full参数，只有拥有dba或者 exp_full_database和imp_full_database权限的用户才能执行。2. Schema模式 导出或导入Schema下的自有对象，对应impdp/expdp命令中的Schema参数，这是默认的操作模式。 如果拥有dba或者 exp_full_database和imp_full_database权限的用户执行的话，就可以导出或导入多个Schema中的对象。3. 表模式 导出指定的表或者表分区（如果有分区的话）以及依赖该表的对象（如该表的索引，约束等，不过前提是这些对象在同一个 Schema中，或者执行的用户有相应的权限）。对应impdp/expdp命令中的Table参数。4. 表空间模式 导出指定的表空间中的内容。对应impdp/expdp中的Tablespaces参数，这种模式类似于表模式和 Schema模式的补充。5. 传输表空间模式 对应impdp/expdp中的Transport_tablespaces参数。这种模式与前面几种模式最显著的区 别是生成的Dump文件中并不包含具 体的逻辑数据，而只导出相关对象的元数据(即对象的定义，可以理解成表的创建语句)，逻辑数据仍然在表空间的数据文件中，导出时需要将元数据和数据文件同 时复制到目标端服务器。 这种导出方式效率很高，时间开销主要是花在复制数据文件产生的I/O上。expdp执行传输表空间模式的导出，用户必须 拥有 exp_full_database角色或者DBA角色。而通过传输表空间模式导入时，用户必须拥有imp_full_database角色或者DBA角色。五、过滤数据 过滤数据主要依赖于Query和Sample两个参数。其中Sample参数主要针对expdp导出功能。5.1 Query与exp命令中的Query功能类似，不过Expdp中，该参数功能得到了增强，控制的粒度更细。Expdp中的Query也是指定类 似where语句来限定记录。语法如下：Query = Schema.Table_name: Query_clause默认 情况如果不指定Schema.table_name,则Query_clause针对所有导出的表有效，或者你可以为每一个表指定不同的 Query_clause，如：导出a表中所有id5的记录，导出b表中所有name=a的记录，则Query的参数应该如下：Query=A:”Where id5,B:”Where name=a”如果Where条件前没有指定Schema名或者表名的话，默认就是针对当前所有要被 导出的表。如：Query=Where id create directory dump_file as /home/oracle/backup;Directory created.SQL grant read,write on directory dump_file to bam;Grant succeeded.如上，生成的DMP文件将会放在/home/oracle/backup目录下。2) 编写一个导出的参数文件。-bash-3.00$ vi expdp_tbs.parDIRECTORY=DUMP_FILEDUMPFILE=expdp_bamdb2bamtest.dmpLOGFILE=expdp_bamdb2bamtest.logCONTENT=METADATA_ONLY如 上，指定了生成的dmp文件名以及日志名（这些都会被放在/home/oracle/backup下），以及导出模式为：metadata_only只 导出结构，不包含数据。3) 执行导出命令-bash-3.00$ expdp bam/bam parfile=expdp_tbs.parExport: Release .0 64bit Production on Friday, 13 August, 2010 16:35:18Copyright (c) 2003, 2007, Oracle. All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release .0 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsStarting “BAM”.”SYS_EXPORT_SCHEMA_01: bam/* parfile=expdp_tbs.parProcessing object type SCHEMA_EXPORT/USERProcessing object type SCHEMA_EXPORT/SYSTEM_GRANTProcessing object type SCHEMA_EXPORT/ROLE_GRANTProcessing object type SCHEMA_EXPORT/DEFAULT_ROLEProcessing object type SCHEMA_EXPORT/TABLESPACE_QUOTAProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/TYPE/TYPE_SPECProcessing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCEProcessing object type SCHEMA_EXPORT/TABLE/TABLEProcessing object type SCHEMA_EXPORT/TABLE/INDEX/INDEXProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type SCHEMA_EXPORT/TABLE/COMMENTProcessing object type SCHEMA_EXPORT/FUNCTION/FUNCTIONProcessing object type SCHEMA_EXPORT/PROCEDURE/PROCEDUREProcessing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTIONProcessing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDUREProcessing object type SCHEMA_EXPORT/VIEW/VIEWProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINTProcessing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEXProcessing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICSProcessing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICSProcessing object type SCHEMA_EXPORT/JOBMaster table “BAM”.”SYS_EXPORT_SCHEMA_01 successfully loaded/unloaded*Dump file set for BAM.SYS_EXPORT_SCHEMA_01 is:/home/oracle/backup/expdp_bamdb2bamtest.dmpJob “BAM”.”SYS_EXPORT_SCHEMA_01 successfully completed at 16:41:264) 将文件传输到8机器上。-bash-3.00$ ftp 8Connected to 8.220 BNM-TEST2 FTPserverready.Name (8:oracle): oracle331 Password required for oracle.Password:230 User oracle logged in.Remote system type is UNIX.Using binary mode to transfer files.ftp bin200 Type set to I.ftp put /home/oracle/backup/expdp_bamdb2bamtest.dmp /export/app22/backup/expdp_bamdb2bamtest.dmp200 PORT command successful.150 Opening BINARY mode data connection for /export/app22/backup/expdp_bamdb2bamtest.dmp.226 Transfer complete.local: /home/oracle/backup/expdp_bamdb2bamtest.dmp remote: /export/app22/backup/expdp_bamdb2bamtest.dmp125329408 bytes sent in 1.4 seconds (89551.55 Kbytes/s)ftp bye221-You have transferred 125329408 bytes in 1 files.221-Total traffic for this session was 125329890 bytes in 1 transfers.221-Thank you for using the FTP service on BNM-TEST2.221 Goodbye.5) 执行导入-bash-3.00$ impdp bam/bam directory=dump_file dumpfile=expdp_bamdb2bamtest.dmp logfile=expdp_bamdb2bamtest.log table_exists_action=replaceImport: Release .0 64bit Production on Friday, 13 August, 2010 16:44:51Copyright (c) 2003, 2007, Oracle. All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release .0 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsMaster table “BAM”.”SYS_IMPORT_FULL_01 successfully loaded/unloadedStarting “BAM”.”SYS_IMPORT_FULL_01: bam/* directory=dump_file dumpfile=expdp_bamdb2bamtest.dmp logfile=expdp_bamdb2bamtest.log table_exists_action=replaceProcessing object type SCHEMA_EXPORT/USERORA-31684: Object type USER:”BAM” already existsProcessing object type SCHEMA_EXPORT/SYSTEM_GRANTProcessing object type SCHEMA_EXPORT/ROLE_GRANTProcessing object type SCHEMA_EXPORT/DEFAULT_ROLEProcessing object type SCHEMA_EXPORT/TABLESPACE_QUOTAProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/TYPE/TYPE_SPECProcessing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCEProcessing object type SCHEMA_EXPORT/TABLE/TABLE6) 如何进入交互模式在这里，我正在执行导入，我想进入交互模式，查看导入的状态。进入交互模式有两种方式，操作步骤如下：i 使用Ctrl+C退出当前模式ii 在命令行模式下，执行Expdp/Impdp命令，同时指定attach参数连接到当前正在制定的导入/导出任务。如：expdp bam/bam attach=SYS_IMPORT_FULL_01Warning:如果没有指定Attach参数，则默认进入当前正在运行的 任务。不过如果当前没有正在指定的任务，而且也没有给Attach赋值，那么就会报Ora-31626错误。当命令行进入交互模式后，会显示如下 界面：Export7) 交互模式的操作在交互模式中，支持下面几种操作。i 查看JOB的运行状态Export statusii 回退到命令行Export continue_clientiii 增加并行Export parallel=4Warning: 在使用导出时，不能直接指定parallel参数，否则可能会遇到ORA-39095错误，因为如果要并行导出，则必须指定多个导出文件，这里的并行导出 是指，多个线程同时工作，同时从数据库中导出多个dmp文件来。在Oracle Database Utilities中有如下解释：Because each active worker process or I/O server process writes exclusively to one file at a time, an insufficient number of files can have adverse effects. Some of the worker processes will be idle while waiting for files, thereby degrading the overall performance of the job. More importantly, if any member of a cooperating group of parallel I/O server processes cannot obtain a file for output, then the export operation will be stopped with an ORA-39095 error. Both situations can be corrected by attaching to the job using the Data Pump Export utility, adding more files using the ADD_FILE command while in interactive mode, and in the case of a stopped job, restarting the job.You can supply multiple file_name specifications as a comma-delimited list or in separate DUMPFILE parameter specifications. If no extension is given for the filename, then Export uses the default file extension of .dmp. The filenames can contain a substitution variable (%U), which implies that multiple files may be generated. The substitution variable is expanded in the resulting filenames into a 2-digit, fixed-width, incrementing integer starting at 01 and ending at 99. If a file specification contains two substitution variables, both are incremented at the same time. For example, exp%Uaa%U.dmp would resolve to exp01aa01.dmp, exp02aa02.dmp, and so forth.iv 停止JOBExport stop_jobv 启动JOBExport start_jobvi 杀掉JOBExport kill_jobvii 退出交互模式Export exit_clientviii 指定文件大小Export filesize=1Gix 帮助Export Help 十一、EXPDP介绍EXPDP命令行选项1. ATTACH该选项用于在客户会话与已存在导出作用之间建立关联.语法如下ATTACH=schema_name.job_nameSchema_name 用于指定方案名,job_name用于指定导出作业名.注意,如果使用ATTACH选项,在命令行除了连接字符串和ATTACH选项外,不能指定任何其他 选项,示例如下:Expdp scott/tiger ATTACH=scott.export_job2. CONTENT该 选项用于指定要导出的内容.默认值为ALLCONTENT=ALL | DATA_ONLY | METADATA_ONLY当设置 CONTENT为ALL时,将导出对象定义及其所有数据.为DATA_ONLY时,只导出对象数据,为METADATA_ONLY时,只导出对象定义Expdp scott/tiger DIRECTORY=dump DUMPFILE=a.dump CONTENT=METADATA_ONLY3. DIRECTORY指定转储文件和日志文件所在的目录DIRECTORY=directory_objectDirectory_object 用于指定目录对象名称.需要注意,目录对象是使用CREATE DIRECTORY语句建立的对象,而不是OS目录Expdp scott/tiger DIRECTORY=dump DUMPFILE=a.dump4. DUMPFILE用于指定转储文 件的名称,默认名称为expdat.dmpDUMPFILE=directory_object:file_name ,.Directory_object 用于指定目录对象名,file_name用于指定转储文件名.需要注意,如果不指定directory_object,导出工具会自动使用 DIRECTORY选项指定的目录对象Expdp scott/tiger DIRECTORY=dump1 DUMPFILE=dump2:a.dmp5. ESTIMATE指定估算被导出表所占用磁盘空间分方法.默认值是BLOCKSESTIMATE=BLOCKS | STATISTICS设置为BLOCKS时,oracle会按照目标对象所占用的数据块个数乘以数据块尺寸估算对象占用的空间,设置为 STATISTICS时,根据最近统计值估算对象占用空间Expdp scott/tiger TABLES=emp ESTIMATE=STATISTICS DIRECTORY=dump DUMPFILE=a.dump6. ESTIMATE_ONLY指定是否只估算导出作业所占用的磁盘空间,默认值为NEXTIMATE_ONLY=Y | N设 置为Y时,导出作用只估算对象所占用的磁盘空间,而不会执行导出作业,为N时,不仅估算对象所占用的磁盘空间,还会执行导出操作.Expdp scott/tiger ESTIMATE_ONLY=y NOLOGFILE=y7. EXCLUDE (具体见2、Exclude导出用户中指定类型的指定对象)该选项用于指定执行操作时释放要排除对象类型或相关对象EXCLUDE=object_type:name_clause ,.Object_type用于指定要排除的对象类型,name_clause用于指定要排除的具体对象.EXCLUDE和 INCLUDE不能同时使用Expdp scott/tiger DIRECTORY=dump DUMPFILE=a.dup EXCLUDE=VIEW8. FILESIZE指定导出文件的最大尺寸,默认为0,(表示文件尺寸没有限制)9. FLASHBACK_SCN指定导出特定SCN时刻的表数据FLASHBACK_SCN=scn_valueScn_value 用于标识SCN值.FLASHBACK_SCN和FLASHBACK_TIME不能同时使用Expdp scott/tiger DIRECTORY=dump DUMPFILE=a.dmp FLASHBACK_SCN=35852310. FLASHBACK_TIME指定导出特定时间点的表数据FLASHBACK_TIME=”TO_TIMESTAMP(time_value)”Expdp scott/tiger DIRECTORY=dump DUMPFILE=a.dmp FLASHBACK_TIME=“TO_TIMESTAMP(25-08-2004 14:35:00,DD-MM-YYYY HH24:MI:SS)”11. FULL指定数据库模式导出,默认为NFULL=Y | N为Y时,标 识执行数据库导出.12. HELP指定是否显示EXPDP命令行选项的帮助信息,默认为N当设置为Y时,会显示导出选 项的帮助信息.Expdp help=y13. INCLUDE（具体见1、Include导出用户中指定类型的指定对象）指 定导出时要包含的对象类型及相关对象INCLUDE = object_type:name_clause , 14. JOB_NAME指定要导出作用的名称,默认为SYS_XXXJOB_NAME=jobname_stringSELECT * FROM DBA_DATAPUMP_JOBS;-查看存在的job15. LOGFILE指定导出日志文件文件的名称,默 认名称为export.logLOGFILE=directory_object:file_nameDirectory_object 用于指定目录对象名称,file_name用于指定导出日志文件名.如果不指定directory_object.导出作用会自动使用DIRECTORY 的相应选项值.Expdp scott/tiger DIRECTORY=dump DUMPFILE=a.dmp logfile=a.log16. NETWORK_LINK指定数据库链名,如果要将远程数据库对象导出到本地例程的转储文件中,必须设置该选项.如：expdp gwm/gwm directory=dir_dp NETWORK_LINK=igisdb tables=p_street_area dumpfile =p_street_area.dmp logfile=p_street_area.log job_name=my_jobigisdb是目的数据库与源数据的链接名，dir_dp是目的数据库上的目录而如果直接用使用 连接字符串(fgisdb),expdp属于服务端工具，expdp生成的文件默认是存放在服务端的17. NOLOGFILE该 选项用于指定禁止生成导出日志文件,默认值为N.18. PARALLEL指定执行导出操作的并行进程个数,默认值为1注： 并行度设置不应该超过CPU数的2倍，如果cpu为2个，可将PARALLEL设为2，在导入时速度比PARALLEL为1要快而对于导出的文件，如果PARALLEL设为2，导出文件只有一个，导出速度提高不多，因为导出都是到同一个文件，会争抢资源。所以可以设置导出文件为两 个,如下所示：expdp gwm/gwm directory=d_test dumpfile=gwmfile1.dp,gwmfile2.dp parallel=219. 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等选项同时使用.Expdp scott/tiger directory=dump dumpfile=a.dmp Tables=emp query=WHERE deptno=2021. SCHEMAS该方案用于指定执行方案模式导出,默认为当前用户方案.22. STATUS指定显示导出作用进程的详细状态,默认值为023. 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=COMPATIBLE | LATEST | version_string为COMPATIBLE时,会根据初始化参数COMPATIBLE生成对象元数据;为 LATEST时,会根据数据库的实际版本生成对象元数据.version_string用于指定数据库版本字符串.调用EXPDP十二、数据泵工具导出的步骤1、创建DIRECTORYcreate directory dir_dp as D:oracledir_dp; 2、授权Grant read,write on directory dir_dp to lttfm;-查看目录及权限SELECT privilege, directory_name, DIRECTORY_PATH FROM user_tab_privs t, all_directories d WHERE t.table_name(+) = d.directory_name ORDER BY 2, 1;3、执行导出expdp lttfm/lttfmfgisdb schemas=lttfm directory=dir_dp dumpfile =expdp_test1.dmp logfile=expdp_test1.log;连 接到: Oracle Database 10g Enterprise Edition Release With the Partitioning, OLAP and Data