Oracle中三个使用Data Pump的小技巧分别是什么，针对这个问题，这篇文章详细介绍了相对应的分析和解答，希望可以帮助更多想解决这个问题的小伙伴找到更简单易行的方法。
数据泵Data Pump是Oracle从10g推出，用于替代传统的exp/imp客户端工具的数据迁移产品。相对于exp/imp工具，数据泵无论是在功能上，还是效率上都有很大提升。本篇主要介绍几个在使用数据泵时候的小技巧，权当记录，供有需要朋友待查。
1、环境介绍和构建
我们选择Oracle 11gR2进行测试，区别于exp/imp工具，数据泵是一个只能运行在服务端的工具。而且directory对象是需要在数据库中额外建立的。
[root@bspdev ~]# cd /
[root@bspdev /]# mkdir dumptest
[root@bspdev /]# ls -l | grep dumptest
drwxr-xr-x 2 root root 4096 Aug 12 03:25 dumptest
[root@bspdev /]# chown -R oracle:oinstall dumptest/
[root@bspdev /]# ls -l | grep dumptest
drwxr-xr-x 2 oracle oinstall 4096 Aug 12 03:25 dumptest
在Oracle中创建directory对象。
SQL> show user
User is "SYS"
SQL> create directory dumptest as '/dumptest';
Directory created
先使用expdp创建出一个dump文件作为实验对象。
[oracle@bspdev dumptest]$ expdp /"/ as sysdba/" directory=dumptest dumpfile=scott.dmp schemas=scott;
Export: Release 11.2.0.1.0 – Production on Mon Aug 12 03:33:36 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
（篇幅原因，有省略……）
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
/dumptest/scott.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 03:34:46
[oracle@bspdev dumptest]$ ls -l
total 107240
-rw-r–r– 1 oracle oinstall 2501 Aug 12 03:34 export.log
-rw-r—– 1 oracle oinstall 109694976 Aug 12 03:34 scott.dmp
我们已经获得了一个用于实验的scott.dmp文件。
2、使用SQLFILE参数抽取数据对象DDL文本
SQLFILE是impdp的一个参数，从官方的定义上，这个参数的含义如下：
SQLFILE
Write all the SQL DDL to a specified file.
如果我们在impdp里面设置了sqlfile参数，就意味着我们将dump文件的导入内容并不是直接导入到数据库中，而是以SQL语句形式输出到文本文件里面。这种方式可以用于抽取ddl语句和测试dump文件可用性。我们进行测试如下：
[oracle@bspdev dumptest]$ impdp /"/ as sysdba/" dumpfile=scott.dmp content=metadata_only sqlfile=scott_ddl.sql directory=dumptest
Import: Release 11.2.0.1.0 – Production on Mon Aug 12 03:39:22 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
（篇幅原因，有省略……）
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYS"."SYS_SQL_FILE_FULL_01" successfully completed at 03:39:26
在directory目录中，我们看到了生成的SQL文件。
[oracle@bspdev dumptest]$ ls -l
total 107320
-rw-r–r– 1 oracle oinstall 2501 Aug 12 03:34 export.log
-rw-r–r– 1 oracle oinstall 1290 Aug 12 03:39 import.log
-rw-r–r– 1 oracle oinstall 72209 Aug 12 03:39 scott_ddl.sql
-rw-r—– 1 oracle oinstall 109694976 Aug 12 03:34 scott.dmp
其中片段如下：
— CONNECT SYS
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
— new object type path: SCHEMA_EXPORT/USER
— CONNECT SYSTEM
CREATE USER "SCOTT" IDENTIFIED BY VALUES 'S:CB685EC52372362B49B7EC43AB0B04BCAF2C71F283C5A558FF8E430F5365;F894844C34402B67'
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP";
— new object type path: SCHEMA_EXPORT/SYSTEM_GRANT
GRANT EXECUTE ANY PROCEDURE TO "SCOTT";
GRANT CREATE VIEW TO "SCOTT";
在sql文本里，我们可以找到所有的对应信息和SQL语句。当然，我们也可以针对某些数据表对象进行DDL导出。
[oracle@bspdev dumptest]$ impdp /"/ as sysdba/" dumpfile=scott.dmp content=metadata_only sqlfile=scott_ddl_single_tb.sql directory=dumptest tables=scott.emp,scott.dept
Import: Release 11.2.0.1.0 – Production on Mon Aug 12 03:49:07 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
（篇幅原因，有省略……）
Job "SYS"."SYS_SQL_FILE_TABLE_01" successfully completed at 03:49:09
[oracle@bspdev dumptest]$ ls -l
total 107332
-rw-r–r– 1 oracle oinstall 2501 Aug 12 03:34 export.log
-rw-r–r– 1 oracle oinstall 1031 Aug 12 03:49 import.log
-rw-r–r– 1 oracle oinstall 9031 Aug 12 03:49 scott_ddl_single_tb.sql
-rw-r–r– 1 oracle oinstall 72209 Aug 12 03:39 scott_ddl.sql
-rw-r—– 1 oracle oinstall 109694976 Aug 12 03:34 scott.dmp
在文本中，我们可以方便的找到建表语句。
— new object type path: SCHEMA_EXPORT/TABLE/TABLE
CREATE TABLE "SCOTT"."DEPT"
( "DEPTNO" NUMBER(2,0),
"DNAME" VARCHAR2(14 BYTE),
"LOC" VARCHAR2(13 BYTE)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;
CREATE TABLE "SCOTT"."EMP"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10 BYTE),
"JOB" VARCHAR2(9 BYTE),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;
那么，我们使用sqlfile的场景是什么呢？从笔者的经验上，有两种场景使用sqlfile是非常合适的。
首先，是字符集转换不一致问题。数据泵Data Pump因为运行在服务端，所以原则上没有很多字符集问题。不像exp/imp在处理的时候需要进行set/export字符集环境变量。但是，当进行单字节和双字节字符集合导入导出的时候，可能发生数据表长度不能匹配数据的情况。在这种时候，我们需要手工的创建数据表调整字段长度来适应变化。此时，使用SQLFILE来生成DDL语句是很方便的，调整之后就可以直接创建。
其次，版本特性差异。我们看到数据表的DDL中，可以看到很多Table Annonation，这些是数据表特性的属性值。在Oracle中，不同版本下，如Enterprise和Standard版本，支持特性是有一些差异的。所以，如果发生源数据库的特性取值在目标数据库中不匹配的现象，就可以借用这个参数进行DDL语句输出，之后调整数据表DDL以适应目标版本。
SQLFILE是一种很方便的工具参数。
3、Parfile参数文件
同exp/imp一样，Data Pump也支持三种工作模式，全库模式（Full）、用户策略（Schema）模式和数据表（Table）模式。
各种模式下，组合参数有一定差异。而且，如果进行复杂的参数条件，如加入了query、include和exclude参数，我们调用expdp/impdp的参数列表就很长了。此时，借用一个key-value设置的参数文件就非常方便。
官方文件中，对parfile的解释如下：
PARFILE
Specify parameter file name.
我们定义一个文本参数文件par.txt。
[oracle@bspdev dumptest]$ cat par.txt
directory=dumptest
schemas=scott
dumpfile=scott_par.dmp
query=scott.emp:"where 1=0"
参数以key=value的方式进行保存，注意query部分与我们用命令行时候的差别。在parfile里面，我们不需要关注字符转义。
[oracle@bspdev dumptest]$ expdp /"/ as sysdba/" parfile=par.txt
Export: Release 11.2.0.1.0 – Production on Mon Aug 12 05:31:36 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01": "/******** AS SYSDBA" parfile=par.txt
（篇幅原因，有省略……）
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
/dumptest/scott_par.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 05:32:05
如果我们进行数据泵参数比较复杂的时候，可以考虑使用parfile保存一定的参数。
4、query参数
Tables、Schemas负责定义对象的范围，而include和exclude负责定义数据操作的类型。而query主要负责定义数据表的那些数据（设置条件）会被导入导出。
下面是query参数的官方定义。
QUERY
Predicate clause used to export a subset of a table.
For example, QUERY=employees:"WHERE department_id > 10".
我们试着将导出的scott数据，导入到一个不存在的用户test里面。注意：Data Pump是会创建对象的。
[oracle@bspdev dumptest]$ impdp /"/ as sysdba/" directory=dumptest remap_schema=scott:test dumpfile=scott.dmp query=scott.t_com:/"where 1=0/"
Import: Release 11.2.0.1.0 – Production on Mon Aug 12 04:07:58 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01": "/******** AS SYSDBA" directory=dumptest remap_schema=scott:test dumpfile=scott.dmp query=scott.t_com:"where 1=0"
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "TEST"."T_COM" 103.3 MB 0 out of 1163088 rows
（篇幅原因，有省略……）
Job "SYS"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 04:08:03
注意几个问题：
首先，如果我们在命令行里面设置参数，就需要注意转义字符的处理。一些如引号之类的对象，需要使用/进行处理。
另外，如果我们有一些数据表不想导入，可以在query里面设置上1=0这样的条件。从而实现灵活的处理方案。
我们看一下实际效果，证明test下的t_com的确没有数据。
SQL> select count(*) from scott.t_com;
COUNT(*)
———-
1163088
SQL> select count(*) from test.t_com;
COUNT(*)
———-
0
最后，我们思考一个问题，就是我们将scott给remap到test用户上了，那么test用户登录怎么办？使用哪个密码？
SQL> conn test/tiger@wilson
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as test
密码和scott一样。
Oracle的Data Pump是我们目前默认使用的数据导入导出工具。了解参数列表，了解工具特性，能够在实际工作中解决很多问题。
关于Oracle中三个使用Data Pump的小技巧分别是什么问题的解答就分享到这里了，希望以上内容可以对大家有一定的帮助，如果你还有很多疑惑没有解开，可以关注亿速云行业资讯频道了解更多相关知识。
原创文章，作者：306829225，如若转载，请注明出处：https://blog.ytso.com/207185.html