數(shù)據(jù)泵導(dǎo)出導(dǎo)入

出于許多原因，我們期望從某個數(shù)據(jù)庫中提取大量數(shù)據(jù)以及關(guān)聯(lián)的對象定義，并且采用一種更容易的方式將這些數(shù)據(jù)載入到另一個數(shù)據(jù)庫中。備份就是其中一個重要原因，另外可能需要在生產(chǎn)與測試環(huán)境之間或聯(lián)機系統(tǒng)與數(shù)據(jù)倉庫之間轉(zhuǎn)移數(shù)據(jù)。數(shù)據(jù)泵Data Pump是用于在Oracle數(shù)據(jù)庫間進行大規(guī)模、快速數(shù)據(jù)傳輸?shù)墓ぞ摺?

1、Data Pump的體系結(jié)構(gòu)

Data Pump是一個服務(wù)器端的實用程序，與過去的Export/Import實用程序相比，運行在服務(wù)器上的Data Pump進程直接訪問數(shù)據(jù)文件和SGA，不必通過會話進行訪問，可以顯著的改善性能。

啟動一個Data Pump作業(yè)時，至少會啟動下列兩個進程：一個Data Pump Master進程（DMnn），一個或多個工作者進程（DWnn）。主進程控制工作者進程。如果啟用并行技術(shù)，那么每個DWnn進程都可以使用兩個或多個并行執(zhí)行服務(wù)器（Pnnn）。

每個Data Pump作業(yè)創(chuàng)建兩個隊列：一個控制隊列和一個狀態(tài)隊列。DMnn進程將任務(wù)放置到控制隊列，DWnn進程獲取并執(zhí)行任務(wù)，同時可能利用并行執(zhí)行服務(wù)器。狀態(tài)隊列用于完成作業(yè)的監(jiān)視功能，由DMnn進程在該隊列中放置作業(yè)的狀態(tài)消息。具有適當(dāng)權(quán)限的任何會話都可以通過查詢這個隊列來監(jiān)視作業(yè)的進度。

Data Pump生成的文件具有下列三種：SQL文件、轉(zhuǎn)儲文件、日志文件。

2、目錄和文件位置

Data Pump始終使用Oracle目錄，用于定位要讀寫的文件及其日志文件。如果沒有在Data Pump中指定目錄，那么將使用默認目錄。每個11g數(shù)據(jù)庫都有一個可供使用的Oracle目錄，其名稱為DATA_PUMP_DIR。

可在四個級別上指定用戶Data Pump作業(yè)的目錄。按照優(yōu)先級從高到低的順序為：

• Data Pump 作業(yè)中每個文件的設(shè)置
• 應(yīng)用于整個 Data Pump 作業(yè)的參數(shù)
• DATA_PUMP_DIR 環(huán)境變量
• DATA_PUMP_DIR 目錄對象

因此，可以明確控制每個文件的位置，可以為作業(yè)指定單個Oracle目錄，可以使用環(huán)境變量，如果這些都行不通，Data Pump將使用默認目錄。

以下創(chuàng)建一個自己的目錄對象

create directory my_dir as 'e:\my_dir';

給目錄授權(quán)

grant read, write on directory my_dir to public;

確認目錄已創(chuàng)建

col directory_path for a60;

select * from dba_directories;

OWNER                          DIRECTORY_NAME                 DIRECTORY_PATH

------------------------------ ------------------------------ ------------------------------------------------------------

SYS                            MY_DIR                         e:\my_dir

SYS                            ORACLECLRDIR                   C:\oracle\app\oracle\product\11.2.0\server\bin\clr

SYS                            DATA_PUMP_DIR                  C:\oracle\app\oracle/admin/xe/dpdump/

SYS                            XMLDIR                         C:\oracle\app\oracle\product\11.2.0\server\rdbms\xml

SYS                            ORACLE_OCM_CONFIG_DIR          C:\ADE\aime_xe28\oracle/ccr/state

3、直接路徑和外部表路徑

Data Pump通過直接路徑或外部表路徑這兩種方式來完成數(shù)據(jù)的加載和卸載。

直接路徑避開了數(shù)據(jù)庫緩沖區(qū)緩存。通過直接路徑導(dǎo)出數(shù)據(jù)時，Data Pump從磁盤直接讀取數(shù)據(jù)文件，提取和格式化文件內(nèi)容，最后將這些文件內(nèi)容寫為一個轉(zhuǎn)儲文件。通過直接路徑導(dǎo)入數(shù)據(jù)時，Data Pump讀取轉(zhuǎn)儲文件，使用文件內(nèi)容填充表數(shù)據(jù)塊，最后將這些內(nèi)容直接寫入數(shù)據(jù)文件。寫操作在表的“高水位線”之上完成。

外部表路徑使用了數(shù)據(jù)庫緩沖區(qū)緩存。導(dǎo)出數(shù)據(jù)時，Data Pump使用普通的SELECT進程將數(shù)據(jù)塊從數(shù)據(jù)文件中讀入緩存，為了輸出為一個轉(zhuǎn)儲文件，會格式化數(shù)據(jù)。導(dǎo)入數(shù)據(jù)時，Data Pump根據(jù)轉(zhuǎn)儲文件的內(nèi)容構(gòu)造標準的INSERT語句，并且通過將數(shù)據(jù)塊從數(shù)據(jù)文件讀至緩存來執(zhí)行這些語句，INSERT操作按照標準的樣式在緩存中完成。如同普通的DML一樣，使用外部表路徑時也會同時生成撤銷和重做。

那么怎樣才能確定Data Pump是使用直接路徑還是外部表路徑呢？DBA并不能對此控制，Data Pump會根據(jù)對象的復(fù)雜性做出決定。只有簡單的結(jié)構(gòu)（如不具有活動觸發(fā)器的堆表）才能通過直接路徑處理。對于更復(fù)雜的對象（如群集表），為了分解復(fù)雜性而要求和SGA進行交互，此時必須使用外部表路徑。在這兩種情況下，生成的轉(zhuǎn)儲文件是完全相同的。

4、使用Data Pump導(dǎo)出和導(dǎo)入

Data Pump可以導(dǎo)出完整的數(shù)據(jù)庫，或數(shù)據(jù)庫的一部分內(nèi)容。可以導(dǎo)出包含或不包含數(shù)據(jù)行的表定義、PL/SQL對象、視圖、序列或其他任何對象類型。如果導(dǎo)出的是表，還可以用WHERE子句限制導(dǎo)出的行，或?qū)С霰淼碾S機百分比抽樣。

可以使用并行處理加快Data Pump的操作速度。并行有兩種級別：工作者進程數(shù)量，以及每個工作者進程使用的并行服務(wù)器數(shù)量。

其評估功能可以在不實際運行作業(yè)的情況下，計算Data Pump導(dǎo)出需要的空間。

在網(wǎng)絡(luò)模式下，即使沒有在磁盤上分段傳輸數(shù)據(jù)，仍然可以使用Data Pump將數(shù)據(jù)集從一個數(shù)據(jù)庫轉(zhuǎn)移到另一個數(shù)據(jù)庫。其實現(xiàn)方式是：源數(shù)據(jù)庫上的Data Pump導(dǎo)出作業(yè)通過數(shù)據(jù)庫鏈接，將數(shù)據(jù)寫入目標數(shù)據(jù)庫，而Data Pump導(dǎo)入作業(yè)從數(shù)據(jù)庫鏈接讀取數(shù)據(jù)，并插入數(shù)據(jù)。網(wǎng)絡(luò)模式下不需要轉(zhuǎn)儲文件目錄，但還是需要日志文件目錄的。

重新映射功能可以重命名對象，可以將對象從一個模式傳輸?shù)搅硪粋€模式，還可以在導(dǎo)入時將數(shù)據(jù)對象從一個表空間移動到另一個表空間。

導(dǎo)出時可以壓縮和加密輸出文件。

導(dǎo)出導(dǎo)入登錄操作的用戶必須具有exp_full_database或imp_full_database的系統(tǒng)權(quán)限。

以下列舉一些常見的用法：

1）導(dǎo)出導(dǎo)入整個數(shù)據(jù)庫

要導(dǎo)出整個數(shù)據(jù)庫，可使用如下命令：

expdp system/mesHz2@mes full=y directory=my_dir parallel=4 dumpfile=full1_%U.dmp,full2_%U.dmp,full3_%U.dmp,full4_%U.dmp filesize=2g compression=all

此命令以system用戶登錄數(shù)據(jù)庫，并使用4個并行運行的工作者進程，每個工作者進程將生成自己的轉(zhuǎn)儲文件集合，這些轉(zhuǎn)儲文件根據(jù)%U模板命名，它將生成唯一的字符串。每個工作者進程將輸出分解為多個不超過2G大小的壓縮文件。

選項compression取值方法對于10g和11g有所不同，10g未描述此選項時默認為壓縮。

對應(yīng)的導(dǎo)入：

impdp system/mes@mes full=y directory=my_dir parallel=4 dumpfile=full1_%U.dmp,full2_%U.dmp,full3_%U.dmp,full4_%U.dmp

在全庫導(dǎo)入的情況下，導(dǎo)入前模式用戶不必先建好，導(dǎo)入時系統(tǒng)會自動重建，只需建立好需要的表空間即可。

2）指定導(dǎo)出導(dǎo)入的模式

可以使用schemas選項指定導(dǎo)出導(dǎo)入的模式：

expdp system/mesHz2@mes schemas=cmes,rmes,bosch,abs directory=my_dir parallel=4 dumpfile=dp1_%U.dmp,dp2_%U.dmp,dp3_%U.dmp,dp4_%U.dmp filesize=2g compression=all

對應(yīng)的導(dǎo)入：

impdp system/mesHz2@mes schemas=cmes,rmes,bosch,abs directory=my_dir parallel=4 dumpfile=dp1_%U.dmp,dp2_%U.dmp,dp3_%U.dmp,dp4_%U.dmp

3）僅導(dǎo)出元數(shù)據(jù)

導(dǎo)出RMES、BOSCH、ABS幾個用戶對象的元數(shù)據(jù)，不包含表的行數(shù)據(jù)：

expdp system/mesHz2@mes schemas=rmes,bosch,abs dumpfile=my_dir:dp_meta.dmp content=metadata_only

對應(yīng)的導(dǎo)入：

impdp system/mesHz2@mes schemas=rmes,bosch,abs dumpfile=my_dir:dp_meta.dmp

4）包含和排除對象

將需要包含或排除的對象在include或exclude子句中列出，以下列出的都是數(shù)據(jù)泵作業(yè)中包含的對象類型（注意觸發(fā)器包含在表類別）：

expdp system/mesHz2@mes schemas=cmes dumpfile=my_dir:dp_cmes.dmp include=user,system_grant,role_grant,default_role,pre_schema,synonym,type,sequence,table,package,function,procedure,view

導(dǎo)出可以按照條件只包含特定的對象和數(shù)據(jù)，排除不需要的對象。表的篩選條件可以是單張或多張表、用like指定的模糊表名，甚至可以通過SQL查詢來指定表名，而數(shù)據(jù)可以通過where條件來指定：

expdp system/mesHz2@mes dumpfile=my_dir:1.dmp tables=rmes.r_wip_print_t

expdp system/mesHz2@mes schemas=rmes dumpfile=my_dir:2.dmp include=table:"in('R_WIP_PRINT_T')"

expdp system/mesHz2@mes schemas=cmes directory=my_dir dumpfile=3.dmp exclude=TABLE;

expdp system/mesHz2@mes schemas=cmes directory=my_dir dumpfile=4.dmp exclude=TABLE:\"LIKE \'TMP%\'\"

expdp system/mesHz2@mes schemas=cmes directory=my_dir dumpfile=5.dmp exclude=TABLE:\"IN \(\'C_LINE_T\'\,\'C_BOM_T\'\)\"

expdp system/mesHz2@mes schemas=cmes directory=my_dir dumpfile=6.dmp exclude=TABLE:\"IN \(select table_name from cmes.k$host_condition where condition <> 'N/A'\)\"

expdp system/mesHz2@mes dumpfile=my_dir:7.dmp tables=rmes.r_wip_tracking_t query="'where in_line_time<=to_date(''2015-01-31 23:59:59'',''yyyy-mm-dd hh34:mi:ss'')'"

如果需要同時導(dǎo)出多張表，并且按照不同條件導(dǎo)出，則可以把這些條件放入一個參數(shù)文件中，用參數(shù)文件來指定數(shù)據(jù)泵的導(dǎo)出作業(yè)，如下一點所述。

導(dǎo)入時可根據(jù)需要考慮是否要先將表truncate，保留其結(jié)構(gòu)，再按條件導(dǎo)入數(shù)據(jù)：

impdp system/mesHz2@mes dumpfile=my_dir:dp.dmp content=data_only tables=rmes.r_wip_tracking_t query="'where in_line_time<=to_date(''2015-01-31 23:59:59'',''yyyy-mm-dd hh34:mi:ss'')'"

可以僅導(dǎo)出指定的表空間：

expdp system/mesHz2@mes directory=my_dir parallel=2 dumpfile=dp1_%u.dmp,dp2_%u.dmp filesize=2g tablespaces=rmes

5）指定數(shù)據(jù)泵作業(yè)的參數(shù)文件

整個作業(yè)還可以通過parfile選項指定到一個參數(shù)文件，便于以后的修改和操作。

如創(chuàng)建一個參數(shù)文件e:\wip_tracking.par，指定按不同條件導(dǎo)出多張表中的數(shù)據(jù)：

job_name=exp_wip_tracking

directory=data_pump_dir

dumpfile=wip_tracking.dmp

tables=rmes.r_wip_tracking_t,rmes.h_wip_tracking_t

query=rmes.r_wip_tracking_t:"where in_line_time between to_date('2015-02-01 00:00:00','yyyy-mm-dd hh34:mi:ss') and to_date('2015-03-31 23:59:59','yyyy-mm-dd hh34:mi:ss')",rmes.h_wip_tracking_t:"where in_line_time between to_date('2015-02-01 00:00:00','yyyy-mm-dd hh34:mi:ss') and to_date('2015-03-31 23:59:59','yyyy-mm-dd hh34:mi:ss')"

導(dǎo)出作業(yè)命令如下：

expdp system/mesHz2@mes parfile=e:\wip_tracking.par

參數(shù)文件的指定必須使用絕對路徑。

6）給作業(yè)定義一個名稱

可以給作業(yè)自定義一個名稱，這樣以后可以通過交互模式管理數(shù)據(jù)泵作業(yè)

expdp system/mesHz2@mes directory=my_dir parallel=2 dumpfile=dp1_%u.dmp,dp2_%u.dmp filesize=2g tablespaces=rmes job_name=exp_rmes_tbs

7）導(dǎo)入時重映射

可以在導(dǎo)入時通過remap_schema選項對模式做重新映射，通過remap_tablespace選項對表空間做重新映射，通過remap_datafile選項對數(shù)據(jù)文件做重新映射。

如下映射進行模式的遷移：

impdp system/mesHz2@mes schemas=rmes dumpfile=my_dir:dp_rmes.dmp remap_schema=rmes:cmes

如下映射進行表空間的遷移：

impdp system/mesHz2@mes schemas=rmes dumpfile=my_dir:dp_rmes.dmp remap_tablespace=rmes:hmes

如下映射重定義數(shù)據(jù)文件的路徑和名稱：

impdp system/mesHz2@mes schemas=scott dumpfile=my_dir:dp_scott.dmp remap_datafile='d:\oradata\mes\users01.ora':'e:\oradata\mes\users01.dbf'

8）抽樣數(shù)據(jù)導(dǎo)出

如下按每個用戶每個表的10%的數(shù)據(jù)比例正態(tài)隨機抽樣導(dǎo)出：

expdp system/mesHz2@mes schemas=rmes,abs directory=my_dir parallel=4 dumpfile=dp1.dmp,dp2.dmp,dp3.dmp,dp4.dmp sample=10

9）轉(zhuǎn)儲導(dǎo)出文件中的DDL定義

可以從導(dǎo)出文件中將DDL定義導(dǎo)入到一個SQL文件中，并不執(zhí)行真正的導(dǎo)入，這樣可以知道備份中發(fā)生了哪些DDL行為：

impdp system/mesHz2@mes dumpfile=my_dir:1.dmp sqlfile=my_dir:1.sql

以下的一組數(shù)據(jù)泵操作可以按指定的篩選條件導(dǎo)出一部分表的DDL定義，其中包含了表的定義，表上索引、約束的定義，還有表的注釋定義，也包含了表的統(tǒng)計信息：

expdp system/mesHz2@mes schemas=cmes dumpfile=my_dir:1.dmp content=metadata_only include=TABLE:\"IN \(select table_name from dis.dis_host_condition where condition is not null\)\"

impdp system/mesHz2@mes dumpfile=my_dir:1.dmp sqlfile=my_dir:1.sql

10）預(yù)估導(dǎo)出文件的大小

可以預(yù)估導(dǎo)出文件的大小，而不執(zhí)行真正的導(dǎo)出：

expdp system/mesHz2@mes schemas=cmes,rmes,bosch,abs estimate_only=y

默認將使用blocks方法根據(jù)表占用的數(shù)據(jù)塊數(shù)量乘上數(shù)據(jù)塊大小來預(yù)估，也可以使用統(tǒng)計信息來預(yù)估，但統(tǒng)計信息要準確：

expdp system/mesHz2@mes schemas=cmes,rmes,bosch,abs estimate_only=y estimate=statistics

11）導(dǎo)入時過濾掉存儲參數(shù)

可以使用transform選項，過濾掉表DDL定義中有關(guān)Storage存儲參數(shù)的定義，解決導(dǎo)入時因存儲參數(shù)中initial初始分配太大導(dǎo)致空間不足的問題：

impdp system/mesHz2@mes schemas=cmes,rmes,bosch,abs dumpfile=my_dir:mes.dmp transform=storage:n

如果將transform選項設(shè)定為segment_attributes:n，則導(dǎo)入時還會去掉表DDL定義中有關(guān)tablespace的定義，從而將所有表改用用戶默認的表空間來存儲：

impdp system/mesHz2@mes schemas=cmes,rmes,bosch,abs dumpfile=my_dir:mes.dmp transform=segment_attributes:n

12）導(dǎo)入時表已存在的處理

可以使用table_exists_action選項指定導(dǎo)入時如果表已存在時執(zhí)行的操作，默認是skip（跳過操作），也可以選擇append（在原來數(shù)據(jù)的基礎(chǔ)上追加數(shù)據(jù)）、replace（表先drop掉，再重建，之后再插入數(shù)據(jù)）或者truncate（表先截斷，再插入數(shù)據(jù)）：

impdp system/mesHz2@mes schemas=cmes,rmes,bosch,abs dumpfile=my_dir:mes.dmp transform=storage:n table_exists_action=replace

除了表的更新能夠replace外，對于其它對象如存儲過程的更新，并不會替換，而是跳過導(dǎo)入。

以下替換導(dǎo)入單張表及其數(shù)據(jù)：

impdp system/mesHz2@mes dumpfile=my_dir:mes.dmp tables=rmes.r_wip_keypart_t table_exists_action=replace

13）導(dǎo)出時覆蓋重名文件

可以使用選項reuse_dumpfiles = y來覆蓋重名文件：

expdp cles/cles schemas = cles directory = data_pump_dir dumpfile = cles.dmp reuse_dumpfiles = y job_name = expdp_cles

14）導(dǎo)出帶有日期時間格式的文件名

可以使用%date:~n1,n2%和%time:~n1,n2%這樣的變量形式定義導(dǎo)出帶有日期時間格式的文件名：

expdp cles/cles schemas = cles directory = data_pump_dir dumpfile = cles%date:~2,2%%date:~5,2%%date:~8,2%%time:~0,2%%time:~3,2%.dmp reuse_dumpfiles = y job_name = expdp_cles

以上導(dǎo)出的文件名格式形如CLES1901131806.DMP這樣的名稱。

15）通過數(shù)據(jù)庫鏈接導(dǎo)出導(dǎo)入

如果本地也建有數(shù)據(jù)庫，則可以通過網(wǎng)絡(luò)訪問遠程數(shù)據(jù)庫服務(wù)端。此時本地作為客戶端，需要在本地tnsnames中建立對應(yīng)的網(wǎng)絡(luò)服務(wù)名，指向遠程數(shù)據(jù)庫服務(wù)端。之后通過如下命令創(chuàng)建數(shù)據(jù)庫鏈接對象：

create public database link dl_mes connect to system identified by mesHz2 using 'mes_1';

這里創(chuàng)建了數(shù)據(jù)庫鏈接對象dl_mes，并設(shè)定以用戶名system和密碼mesHz2連接遠程數(shù)據(jù)庫，網(wǎng)絡(luò)服務(wù)名為mes_1。

查看創(chuàng)建的數(shù)據(jù)庫鏈接對象：

col object_name for a30

select owner, object_name from dba_objects where object_type='DATABASE LINK';

OWNER                          OBJECT_NAME

------------------------------ ------------------------------

PUBLIC                         DL_MES

詳細的信息則可以通過dba_db_links查看：

col owner for a20

col db_link for a20

col username for a20

col host for a20

select * from dba_db_links;

OWNER                DB_LINK              USERNAME             HOST                 CREATED

-------------------- -------------------- -------------------- -------------------- -----------

PUBLIC               DL_MES               SYSTEM               mes_1                2016/11/19

可以通過數(shù)據(jù)庫鏈接對象訪問遠程機器：

select * from scott.emp@dl_mes;

可以通過數(shù)據(jù)庫鏈接對象執(zhí)行遠程數(shù)據(jù)庫的expdp導(dǎo)出，dump文件到本地目錄：

expdp system/mesHz2@mes network_link=dl_mes schemas=cmes,rmes,bosch,abs directory=my_dir parallel=4 dumpfile=dp1_%U.dmp,dp2_%U.dmp,dp3_%U.dmp,dp4_%U.dmp filesize=2g

甚至可以省去expdp這一步，直接通過impdp將遠程數(shù)據(jù)庫對象導(dǎo)入到本地系統(tǒng)：

impdp system/mesHz2@mes network_link=dl_mes schemas=cmes,rmes,bosch,abs

16）指定是否需要創(chuàng)建日志

數(shù)據(jù)泵作業(yè)默認會生成日志文件，如果不想生成日志文件，可以通過nologfile選項指定：

impdp system/mesHz2 network_link=dl_mes_0 schemas=cmes include=procedure nologfile=y

17）會話交互模式

數(shù)據(jù)泵的導(dǎo)出導(dǎo)入可以切換到會話交互模式下進行，此時用戶可以干預(yù)導(dǎo)出導(dǎo)入過程，穿插進前臺的會話而不影響后臺的數(shù)據(jù)泵作業(yè)。

在導(dǎo)出導(dǎo)入過程中按下ctrl-C可以切入到會話交互模式，常用有以下交互命令可以操作：

add_file：增加一個文件到導(dǎo)出目錄中，如：

Export>add_file=my_dir:dp02.dmp

stop_job：停止數(shù)據(jù)泵作業(yè)，如果設(shè)置stop_job=immediate將立即關(guān)閉數(shù)據(jù)泵作業(yè)，此時導(dǎo)出服務(wù)器進程終止。如果需要重新啟動該作業(yè)，則需要根據(jù)作業(yè)名使用attach命令重新開啟作業(yè)，如：

expdp system/mesHz2@mes attach=system.sys_export_full_01

start_job：重新恢復(fù)由于某種意外而停止的數(shù)據(jù)泵作業(yè)。

kill_job：殺掉客戶機進程和數(shù)據(jù)泵作業(yè)的服務(wù)器進程。

continue_client：退出交互方式，恢復(fù)正在運行的數(shù)據(jù)泵作業(yè)，實際的數(shù)據(jù)泵作業(yè)不受影響。

exit_client：終止客戶機交互式會話，用戶可以在當(dāng)前窗口進行其它操作，但實際的數(shù)據(jù)泵作業(yè)不受影響。

parallel：說明當(dāng)前活動作業(yè)的工作者線程數(shù)量。

status：監(jiān)視當(dāng)前作業(yè)的狀態(tài)，參數(shù)設(shè)置為一個整數(shù)值秒數(shù)，如status=60，則每60秒會刷新一次作業(yè)狀態(tài)信息。