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

Oracle中三個使用Data Pump的小技巧分別是什么，針對這個問題，這篇文章詳細介紹了相對應(yīng)的分析和解答，希望可以幫助更多想解決這個問題的小伙伴找到更簡單易行的方法。

數(shù)據(jù)泵Data Pump是Oracle從10g推出，用于替代傳統(tǒng)的exp/imp客戶端工具的數(shù)據(jù)遷移產(chǎn)品。相對于exp/imp工具，數(shù)據(jù)泵無論是在功能上，還是效率上都有很大提升。本篇主要介紹幾個在使用數(shù)據(jù)泵時候的小技巧，權(quán)當(dāng)記錄，供有需要朋友待查。

1、環(huán)境介紹和構(gòu)建

我們選擇Oracle 11gR2進行測試，區(qū)別于exp/imp工具，數(shù)據(jù)泵是一個只能運行在服務(wù)端的工具。而且directory對象是需要在數(shù)據(jù)庫中額外建立的。

[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中創(chuàng)建directory對象。

SQL> show user

User is "SYS"

SQL> create directory dumptest as '/dumptest';

Directory created

先使用expdp創(chuàng)建出一個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

我們已經(jīng)獲得了一個用于實驗的scott.dmp文件。

2、使用SQLFILE參數(shù)抽取數(shù)據(jù)對象DDL文本

SQLFILE是impdp的一個參數(shù)，從官方的定義上，這個參數(shù)的含義如下：

SQLFILE

Write all the SQL DDL to a specified file.

如果我們在impdp里面設(shè)置了sqlfile參數(shù)，就意味著我們將dump文件的導(dǎo)入內(nèi)容并不是直接導(dǎo)入到數(shù)據(jù)庫中，而是以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文本里，我們可以找到所有的對應(yīng)信息和SQL語句。當(dāng)然，我們也可以針對某些數(shù)據(jù)表對象進行DDL導(dǎo)出。

[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的場景是什么呢？從筆者的經(jīng)驗上，有兩種場景使用sqlfile是非常合適的。

首先，是字符集轉(zhuǎn)換不一致問題。數(shù)據(jù)泵Data Pump因為運行在服務(wù)端，所以原則上沒有很多字符集問題。不像exp/imp在處理的時候需要進行set/export字符集環(huán)境變量。但是，當(dāng)進行單字節(jié)和雙字節(jié)字符集合導(dǎo)入導(dǎo)出的時候，可能發(fā)生數(shù)據(jù)表長度不能匹配數(shù)據(jù)的情況。在這種時候，我們需要手工的創(chuàng)建數(shù)據(jù)表調(diào)整字段長度來適應(yīng)變化。此時，使用SQLFILE來生成DDL語句是很方便的，調(diào)整之后就可以直接創(chuàng)建。

其次，版本特性差異。我們看到數(shù)據(jù)表的DDL中，可以看到很多Table Annonation，這些是數(shù)據(jù)表特性的屬性值。在Oracle中，不同版本下，如Enterprise和Standard版本，支持特性是有一些差異的。所以，如果發(fā)生源數(shù)據(jù)庫的特性取值在目標數(shù)據(jù)庫中不匹配的現(xiàn)象，就可以借用這個參數(shù)進行DDL語句輸出，之后調(diào)整數(shù)據(jù)表DDL以適應(yīng)目標版本。

SQLFILE是一種很方便的工具參數(shù)。

3、Parfile參數(shù)文件

同exp/imp一樣，Data Pump也支持三種工作模式，全庫模式（Full）、用戶策略（Schema）模式和數(shù)據(jù)表（Table）模式。

各種模式下，組合參數(shù)有一定差異。而且，如果進行復(fù)雜的參數(shù)條件，如加入了query、include和exclude參數(shù)，我們調(diào)用expdp/impdp的參數(shù)列表就很長了。此時，借用一個key-value設(shè)置的參數(shù)文件就非常方便。

官方文件中，對parfile的解釋如下：

PARFILE

Specify parameter file name.

我們定義一個文本參數(shù)文件par.txt。

[oracle@bspdev dumptest]$ cat par.txt

directory=dumptest

schemas=scott

dumpfile=scott_par.dmp

query=scott.emp:"where 1=0"

參數(shù)以key=value的方式進行保存，注意query部分與我們用命令行時候的差別。在parfile里面，我們不需要關(guān)注字符轉(zhuǎn)義。

[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

如果我們進行數(shù)據(jù)泵參數(shù)比較復(fù)雜的時候，可以考慮使用parfile保存一定的參數(shù)。

4、query參數(shù)

Tables、Schemas負責(zé)定義對象的范圍，而include和exclude負責(zé)定義數(shù)據(jù)操作的類型。而query主要負責(zé)定義數(shù)據(jù)表的那些數(shù)據(jù)（設(shè)置條件）會被導(dǎo)入導(dǎo)出。

下面是query參數(shù)的官方定義。

QUERY

Predicate clause used to export a subset of a table.

For example, QUERY=employees:"WHERE department_id > 10".

我們試著將導(dǎo)出的scott數(shù)據(jù)，導(dǎo)入到一個不存在的用戶test里面。注意：Data Pump是會創(chuàng)建對象的。

[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

注意幾個問題：

首先，如果我們在命令行里面設(shè)置參數(shù)，就需要注意轉(zhuǎn)義字符的處理。一些如引號之類的對象，需要使用\進行處理。

另外，如果我們有一些數(shù)據(jù)表不想導(dǎo)入，可以在query里面設(shè)置上1=0這樣的條件。從而實現(xiàn)靈活的處理方案。

我們看一下實際效果，證明test下的t_com的確沒有數(shù)據(jù)。

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是我們目前默認使用的數(shù)據(jù)導(dǎo)入導(dǎo)出工具。了解參數(shù)列表，了解工具特性，能夠在實際工作中解決很多問題。

關(guān)于Oracle中三個使用Data Pump的小技巧分別是什么問題的解答就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，如果你還有很多疑惑沒有解開，可以關(guān)注億速云行業(yè)資訊頻道了解更多相關(guān)知識。