Oracle中三個使用Data Pump的小技巧分別是什么

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的小技巧分別是什么問題的解答就分享到這里了，希望以上內容可以對大家有一定的幫助，如果你還有很多疑惑沒有解開，可以關注億速云行業資訊頻道了解更多相關知識。