Oracle中三个使用Data Pump的小技巧分别是什么

发布时间：2021-11-09 16:01:53 作者：柒染
来源：亿速云阅读：120

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

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

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

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

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

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(*)

----------

最后，我们思考一个问题，就是我们将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的小技巧分别是什么问题的解答就分享到这里了，希望以上内容可以对大家有一定的帮助，如果你还有很多疑惑没有解开，可以关注亿速云行业资讯频道了解更多相关知识。

Oracle中三个使用Data Pump的小技巧分别是什么

相关阅读