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 提供四种导入导出的方式
- 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
- 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
- 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
- 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)导出导入方式
- 新建一个目录存放导出的数据文件
在 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;
- 导出数据
导出 HPM_DEV 的数据存放在服务器地址中
expdp HPM_DEV/{password}@ORCL schemas=HPM_DEV directory=TEST_DIR dumpfile=HPM_DEV.dmp logfile=expdpHPM_DEV.log
导出数据比较简单,一般来说也比较顺利。
- 导入数据
这里最麻烦的地方是:
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
最后顺利导入成功。