Oracle数据泵(EXPDP和IMPDP)的使用

一、 数据泵与传统导入导出工具比较

        在10g之前,传统的导出和导入分别使用EXP工具和IMP工具,从10g开始,不仅保留了原有的EXP和IMP工具,还提供了数据泵导出导入工具EXPDP和IMPDP.使用EXPDP和IMPDP时应该注意的事项：

• 1）EXP和IMP是客户端工具程序,它们既可以在可以客户端使用,也可以在服务端使用；
• 2）EXPDP和IMPDP是服务端的工具程序,他们只能在ORACLE服务端使用,不能在客户端使用；
• 3）IMP只适用于EXP导出文件,不适用于EXPDP导出文件;IMPDP只适用于EXPDP导出文件,而不适用于EXP导出文件；
• 4）expdp/impdp 支持并行操作，在资源允许的情况下可以提高效率；
• 5）数据泵导入工具提供remap选项

可以将已有数据导入到不同的表空间，不同用户。

数据泵导出包括4种方式：

• 1）导出表
• 2）导出方案
• 3）导出表空间
• 4）导出数据库

二、 数据泵使用前准备

2.1 在服务器端，创建用于存放dmp 文件的目录

示例如下：

mkdir -p /install/dmp

chown -R oracle:dba /install/dmp

由于数据泵为服务端工具，因此需要oracle 用户在此目录有读写权限。否则在执行时，会产生报错。报错信息如下：

ORA-39002: invalid operationORA-39070: Unable to open the log file.ORA-29283: invalid file operationORA-06512: at "SYS.UTL_FILE", line 536ORA-29283: invalid file operation

2.2 在数据库中创建导入导出用directory

create directory test_dmp as '/install/dmp';

如果directory 不存在，在执行数据泵导入导出任务时，会遇到如下报错：

• ORA-39002: 操作无效
• ORA-39070: 无法打开日志文件
• ORA-39087: 目录名 DIR1 无效

2.3 检查系统是否已存在directory

select owner,directory_name,directory_path from dba_directories;

2.4 检查directory权限

select grantee,owner,table_name,privilegefrom dba_tab_privswhere table_name = 'test_dmp' order by 1,2;

如果没有足够的权限，在执行数据泵导入导出任务时，会遇到如下报错：

• ORA-39002: 操作无效
• ORA-39070: 无法打开日志文件
• ORA-39087 目录名 DIR1 无效

2.5 对directory 进行授权

grant read,write on directory test_dmp to public ;

授权给public ，那么所有用户都可以使用。如果只授权给指定用户，那么只有该用户可以使用。

2.6 简单示例(注意：要切换回shell窗口，不要再在sql>下运行，然后输入命令)

1）数据导出

expdp user01/user01_123 directory=test_dmp dumpfile=t2.dmp log=expt2.log

产生文件及其权限信息如下：

-rw-r----- 1 oracle asmadmin 52183040 Dec 18 17:04 t1.dmp
-rw-r--r-- 1 oracle asmadmin 1789 Dec 18 17:04 expt1.log

2）数据导入

impdp user01/user01_123 directory=test_dmp dumpfile=t2.dmp schemas=user01 log=impt2.log

三、 数据泵导出场景实例(注意：要切换回shell窗口，不要再在sql>下运行，然后输入命令)

3.1 全数据库导出

执行此项任务，用户需要exp_full_database 权限，否则系统会产生如下报错：

• ORA-31631: 需要权限
• ORA-39161: 完整数据库作业需要权限

示例如下：

expdp user01/user01_123 directory= test_dmp dumpfile=dmp1.dmp logfile=dmp1.log full=y

3.2 并行全库导出

当系统有多个CPU时，可启用并行。进程的数量，应与导出文件的数量一致。同时也可指定导出文件的最大尺寸。如果导出文件数量不定，可使用%U。

示例如下：

expdp user01/user01_123 directory=test_dmp dumpfile=dmp_para%U.dmp parallel=2 filesize=20M logfile=parallel.log full=y

3.3 导出指定用户（schema）数据

示例如下：

expdp user01/user01_123 directory=test_dmp dumpfile=dmp_schema.dmp schemas=user01 logfile=dmp_schema.log

3.4 按表空间进导出

示例如下：

expdp user01/user01_123 directory=test_dmpdumpfile=dmp_tbs%U.dmp tablespaces=users,sysaux parallel=2 filesize=20M logfile=dmptbs.log

3.5 导出表数据

expdp user01/user01_123 directory=test_dmp dumpfile=dmp_tabs%U.dmp tables=t1,t2 filesize=20M logfile=dmptabs.log

3.6 使用查询条件导出表数据

此时需注意单引号，双引号的使用，否则会出现如下报错：

• ORA-39001: 参数值无效
• ORA-39035: 已经指定了数据过滤器 SUBQUERY

示例如下：

expdp lirl/lirl directory=dump_dir dumpfile=dmp_QUERY%U.dmpQUERY=T1:\"WHERE OBJECT_NAME LIKE '%TAB%'\" parallel=2 filesize=20M logfile=dmpQUERY.log

3.7 只导出元数据

示例如下：

expdp user01/user01_123 directory=test_dmpdumpfile=dmp_metadata.dmp content=metadata_only logfile=dmpmetadata.log

3.8 排除部分表

 expdp cnoocmdm@191 directory=dir_dp dumpfile=20181018.dmp logfile=20181018.log EXCLUDE=TABLE:\"IN \(\'LOG_MESSAGE_ARC\',\'BASE_UPLOADFILEBLOB\',\'LOG_MESSAGE\',\'JBPM_VARIABLEINSTANCE_BAK\',\'NDN_YHBS_CODESYN\',\'NDN_CODESYN_LOG\'\)\"

四、 数据泵导入场景实例

数据泵导入的基本语法与数据泵导出基本相同。

4.1 导入到不同表空间

impdp lirl/lirl directory=dump_dir dumpfile=dmp_para%U.dmp schemas=test remap_tablespace=users:test_tbs logfile=imp1.log

4.2 表数据已经存在时导入

在表数据已经存在时，table_exists_action 用于对已有数据的处理。Skip 跳过已有数据，在导入部分数据任务失败场景下，可使用该选项，避免重复导入数据，加快进度；

• Truncate 删除已有数据，重新插入
• Append 追加数据
• Replace 替换现有数据

impdp lirl/lirl directory=dump_dir dumpfile=dmp_para%U.dmpschemas=test remap_tablespace=users:test_tbs table_exists_action=skip logfile=imp1.log

4.3 提取 ddl 语句

impdp lirl/lirl directory=dump_dir dumpfile=DMP_SCHEMA.DMP schemas=test sqlfile='ddl.sql' logfile=imp1.log

五、 导入导出任务维护

5.1 查询正在运行的数据泵任务

SELECT A.INST_ID, A.SID||','||A.SERIAL# AS SESS, B.OWNER_NAME, B.JOB_NAME, A.STATUS, A.SADDR, TO_CHAR(A.LOGON_TIME,'YYYYMMDD HH24:MI:SS') AS LOGON_TM FROM GV$SESSION A, DBA_DATAPUMP_SESSIONS BWHERE A.INST_ID = B.INST_ID AND A.SADDR = B.SADDRORDER BY 1,2,4;

5.2 停止数据泵任务

由于数据泵为服务端工具，因此简单的退出impdp/expdp命令，并不能真正停止该任务。应按照如下步骤进行：

• 1）使用检查数据泵任务语句获取该任务的job_name ;
• 2）impdp/expdp user/password attach=job_name
• 3）stop_job (该命令暂停数据泵任务)
• 4）kill_job(停止数据泵任务)

六 错误场景实例

6.1 导出失败的情况：

• 1）磁盘空间不足
• 2）Flash_scn手动指定时间点，额外开销，引起比较慢，到最后报错
• 3）Exp如果网络不稳定，影响导出
• 4）Oracle bug引起导出失败
• 5）stream pool size太小会引起报错39097
• 6）并行导出

当通过expdp/impdp导出导入时，若指定了content=metadata_only选项，则导入时表的统计信息会被锁定(即dba_tab_statistics视图中STATTYPE_LOCKED列值为ALL)。

同样，使用传统的exp/imp作导出导入时，若指定了rows=n，则也存在同样的问题。

若导入同时明确指定不导入统计信息，如EXCLUDE=TABLE_STATISTICS，则不存在以上问题。

解决办法：导入后，使用dbms_stats包手工解锁。

Oracle这么做的原因是：

从10g开始，Oracle设置了统计信息自动收集任务。当指定metadata_only导入时，表内暂时没有数据。若此时间窗口内统计信息自动收集任务启动，会导致数据库收集了严重失真的统计信息，从而影响上层sql语句的性能。为了规避该问题，Oracle默认采用了上述解决方案。

6.2 ORA-39112

导出正常，导入数据时，只成功导入部分记录等数据，另外的部分提数ora 39112错误，经查是因为导出的用户数据中，有部分记录的表用的索引在另一表空间中，该空间还未创建，所以导致该失败。

解决方法：在导入时，添加参数：RANSFORM=segment_attributes:n ，配合table_exists_action=replace参数，重新导入即可。

RANSFORM=segment_attributes:n 在导入时，会将数据导入默认的表空间中。

 补充，造成该问题的可能原因：

1、在原来测试库中，目标schema和别的用户相互授权了，可是你导出的dmp中没有包含所有的用户，导入时对应用户没有创建。 2、再就是，表空间问题，测试库中的用户下的某个表的索引没有在他的默认表空间里，这样你要在目标端（这里就是生产环境），创建好对应的表空间， 就是说如果你在测试库把a用户的下的某个表的权限授给了b,那么你在把a用户用数据泵倒进生产库时，他会在生产库中检测有没有用户ｂ。也要做相同的操作。

6.3 oracle导入导出(impdp/expdp)高版本导入低版本

        高版本导出的dmp在低版本导入的问题，如果是使用impdp/empdp来进行导出和导入数据库的话，在高版本的数据库中导出dmp文件时需要在末尾增加要导入的数据库的版本号(低版本oracle):

expdp user01/user01_123 directory=test_dmp dumpfile=dmp_para%U.dmp parallel=2 filesize=20M logfile=parallel.log full=y version=11.2.0.1.0