Loading

Oracle expdp impdp

Oracle Data Pump (expdp, impdp) 是 Oracle 10g 之后中新增的一个功能，用于导出和导入数据，速度非常快，适用于 10g, 11g, 12c, 18c, 19c 版本。

实际使用 expdp/impdp 工具的过程中中碰到了不少问题，做个记录，以备不时之需。

本次操作环境：

  • Oracle 版本：11g Enterprise Edition Release 11.2.0.1.0 - 64bit
  • Linux 版本：CentOS x86_64（内核版本Linux 3.10.0-327.el7.x86_64）
  • 导出数据库用户为：HPM_DEV，表空间为：TBS_PERM_HPM_DEV
  • 导入数据库用户为：HPM_UAT，表空间为：TBS_PERM_HPM_UAT

Oracle expdp/impdp 提供四种导入导出的方式

  1. Table Exports/Imports（按表导出导入）

官方示例：

expdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log

impdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=impdpEMP_DEPT.log
  1. Schema Exports/Imports（按用户导出导入）

官方示例；

expdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log

impdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=impdpSCOTT.log
  1. Database Exports/Imports（按数据库全量导出导入）

官方示例；

expdp system/password@db10g full=Y directory=TEST_DIR dumpfile=DB10G.dmp logfile=expdpDB10G.log

impdp system/password@db10g full=Y directory=TEST_DIR dumpfile=DB10G.dmp logfile=impdpDB10G.log
  1. INCLUDE and EXCLUDE（按条件导出导入）

基本语法：

INCLUDE=object_type[:name_clause] [, ...]

EXCLUDE=object_type[:name_clause] [, ...]

官方示例；

expdp scott/tiger@db10g schemas=SCOTT include=TABLE:"IN ('EMP', 'DEPT')" directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log

expdp scott/tiger@db10g schemas=SCOTT exclude=TABLE:"= 'BONUS'" directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log

本次使用的是按用户(scheme)导出导入方式

  1. 新建一个目录存放导出的数据文件

在 SQLPlus 中操作，创建一个逻辑目录，映射一个物理地址，导出的文件就存放在这个物理地址中：

CREATE OR REPLACE DIRECTORY TEST_DIR AS '/u01/app/oracle/oradata/';

然后分别为导出用户和导入用户授权读写这个目录：

GRANT READ, WRITE ON DIRECTORY TEST_DIR TO HPM_DEV;
GRANT READ, WRITE ON DIRECTORY TEST_DIR TO HPM_UAT;
  1. 导出数据

导出 HPM_DEV 的数据存放在服务器地址中

expdp HPM_DEV/{password}@ORCL schemas=HPM_DEV directory=TEST_DIR dumpfile=HPM_DEV.dmp logfile=expdpHPM_DEV.log

导出数据比较简单，一般来说也比较顺利。

  1. 导入数据

这里最麻烦的地方是：

HPM_DEV 用户使用的表空间是 TBS_PERM_HPM_DEV，要导入的用户是 HPM_UAT，而表空间是 TBS_PERM_HPM_UAT

因此导入的时候需要将用户/schemes和表空间进行转换

这里需要用到两个参数：REMAP_SCHEMA 和 REMAP_TABLESPACE

  • REMAP_SCHEMA：Loads all objects from the source schema into a target schema.

就是将导出的 scheme（HPM_DEV）转换成另一个被导入的 scheme（HPM_UAT）

语法如下：

REMAP_SCHEMA = source_schema:target_schema
  • REMAP_TABLESPACE：Remaps all objects selected for import with persistent data in the source tablespace to be created in the target tablespace.

和上面转换 scheme 一样，这个命令的作用就是转换表空间

最终导入的 sql 为：

impdp HPM_UAT/{password}@ORCL REMAP_SCHEMA=HPM_DEV:HPM_UAT REMAP_TABLESPACE=TBS_PERM_HPM_DEV:TBS_PERM_HPM_UAT directory=TEST_DIR dumpfile=HPM_DEV.dmp logfile=impdpHPM_UAT.log

命令运行到最后出了一个很奇怪的错误：

ORA-39126: Worker unexpected fatal error in KUPW$WORKER.PUT_DDLS [TABLE_STATISTICS] 

ORA-06502: PL/SQL: numeric or value error

LPX-00242: invalid use of ampersand ('&') character (use &)

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95

ORA-06512: at "SYS.KUPW$WORKER", line 9001

网友说是 Oracle 11g 的一个bug，在导入语句中加入一个参数：EXCLUDE=STATISTICS即可解决

最终导入语句为：

impdp HPM_UAT/{password}@ORCL REMAP_SCHEMA=HPM_DEV:HPM_UAT REMAP_TABLESPACE=TBS_PERM_HPM_DEV:TBS_PERM_HPM_UAT directory=TEST_DIR dumpfile=HPM_DEV.dmp logfile=impdpHPM_UAT.log EXCLUDE=STATISTICS

最后顺利导入成功。

posted @ 2021-04-15 20:04  LifeLogs  阅读(214)  评论(0)  编辑  收藏  举报