資料泵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(*)

----------

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一樣。

5、結論

Oracle的Data Pump是我們目前預設使用的資料匯入匯出工具。瞭解引數列表，瞭解工具特性，能夠在實際工作中解決很多問題。

三個使用資料泵（Data Pump）的小技巧

相關文章