数据泵使用手册

IT那活儿 2023-10-26

917

点击上方“IT那活儿”公众号，关注后了解更多内容，不管IT什么活儿，干就完了！！！

一

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

在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 operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-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

set lines 200 pages 1000
col owner format a20
col directory_name format a30
col directory_path format a60
select owner,directory_name,directory_path from dba_directories ;

2.4 检查directory权限

set lines 200 pages 1000
col owner format a20
col grantee format a20
col table_name format a30
col PRIVILEGE format a30
select grantee,
owner,
table_name,
privilege
from dba_tab_privs
where table_name = '&directory_name'
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 简单示例

1）数据导出

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

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

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

2）数据导入

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

三

数据泵导出场景实例

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_dmp 
dumpfile=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.dmp 
QUERY=T1:\"WHERE OBJECT_NAME LIKE '%TAB%'\" parallel=2 filesize=20M logfile=dmpQUERY.log

3.7 只导出元数据

示例如下：

expdp user01/user01_123 directory=test_dmp 
dumpfile=dmp_metadata.dmp content=metadata_only logfile=dmpmetadata.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.dmp 
schemas=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 查询正在运行的数据泵任务

SET LINES 200 PAGES 1000
COL INST_ID FORMAT 9999
COL SESS FORMAT A24
COL OWNER_NAME FORMAT A16
COL JOB_NAME FORMAT A20
COL STATUS         FORMAT A12
COL LOGON_TM FORMAT A20
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 B
WHERE A.INST_ID = B.INST_ID
  AND A.SADDR = B.SADDR 
ORDER 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(停止数据泵任务)

六

错误场景实例

导出失败的情况：

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

Version=

当通过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默认采用了上述解决方案。

END

本文作者：张芳(上海新炬中北团队）

本文来源：“IT那活儿”公众号

文章转载自IT那活儿，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

数据泵使用手册

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

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

2.3 检查系统已经存directory

2.4 检查directory权限

2.5 对directory 进行授权

2.6 简单示例

1）数据导出

2）数据导入

3.1 全数据库导出

3.2 并行全库导出

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

3.4 按表空间进导出

3.5 导出表数据

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

3.7 只导出元数据

4.1 导入到不同表空间

4.2 表数据已经存在时导入

4.3 提取 ddl 语句

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

5.2 停止数据泵任务

本文作者：张 芳(上海新炬中北团队）

本文来源：“IT那活儿”公众号

评论

本文作者：张芳(上海新炬中北团队）