數據泵導出導入

出于許多原因，我們期望從某個數據庫中提取大量數據以及關聯的對象定義，并且采用一種更容易的方式將這些數據載入到另一個數據庫中。備份就是其中一個重要原因，另外可能需要在生產與測試環境之間或聯機系統與數據倉庫之間轉移數據。數據泵Data Pump是用于在Oracle數據庫間進行大規模、快速數據傳輸的工具。

1、Data Pump的體系結構

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

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

每個Data Pump作業創建兩個隊列：一個控制隊列和一個狀態隊列。DMnn進程將任務放置到控制隊列，DWnn進程獲取并執行任務，同時可能利用并行執行服務器。狀態隊列用于完成作業的監視功能，由DMnn進程在該隊列中放置作業的狀態消息。具有適當權限的任何會話都可以通過查詢這個隊列來監視作業的進度。

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

2、目錄和文件位置

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

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

• Data Pump 作業中每個文件的設置
• 應用于整個 Data Pump 作業的參數
• DATA_PUMP_DIR 環境變量
• DATA_PUMP_DIR 目錄對象

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

以下創建一個自己的目錄對象

create directory my_dir as 'e:\my_dir';

給目錄授權

grant read, write on directory my_dir to public;

確認目錄已創建

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通過直接路徑或外部表路徑這兩種方式來完成數據的加載和卸載。

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

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

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

4、使用Data Pump導出和導入

Data Pump可以導出完整的數據庫，或數據庫的一部分內容。可以導出包含或不包含數據行的表定義、PL/SQL對象、視圖、序列或其他任何對象類型。如果導出的是表，還可以用WHERE子句限制導出的行，或導出表的隨機百分比抽樣。

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

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

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

重新映射功能可以重命名對象，可以將對象從一個模式傳輸到另一個模式，還可以在導入時將數據對象從一個表空間移動到另一個表空間。

導出時可以壓縮和加密輸出文件。

導出導入登錄操作的用戶必須具有exp_full_database或imp_full_database的系統權限。

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

1）導出導入整個數據庫

要導出整個數據庫，可使用如下命令：

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用戶登錄數據庫，并使用4個并行運行的工作者進程，每個工作者進程將生成自己的轉儲文件集合，這些轉儲文件根據%U模板命名，它將生成唯一的字符串。每個工作者進程將輸出分解為多個不超過2G大小的壓縮文件。

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

對應的導入：

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

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

2）指定導出導入的模式

可以使用schemas選項指定導出導入的模式：

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

對應的導入：

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）僅導出元數據

導出RMES、BOSCH、ABS幾個用戶對象的元數據，不包含表的行數據：

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

對應的導入：

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

4）包含和排除對象

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

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

導出可以按照條件只包含特定的對象和數據，排除不需要的對象。表的篩選條件可以是單張或多張表、用like指定的模糊表名，甚至可以通過SQL查詢來指定表名，而數據可以通過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'')'"

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

導入時可根據需要考慮是否要先將表truncate，保留其結構，再按條件導入數據：

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'')'"

可以僅導出指定的表空間：

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

5）指定數據泵作業的參數文件

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

如創建一個參數文件e:\wip_tracking.par，指定按不同條件導出多張表中的數據：

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')"

導出作業命令如下：

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

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

6）給作業定義一個名稱

可以給作業自定義一個名稱，這樣以后可以通過交互模式管理數據泵作業

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）導入時重映射

可以在導入時通過remap_schema選項對模式做重新映射，通過remap_tablespace選項對表空間做重新映射，通過remap_datafile選項對數據文件做重新映射。

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

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

如下映射重定義數據文件的路徑和名稱：

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）抽樣數據導出

如下按每個用戶每個表的10%的數據比例正態隨機抽樣導出：

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

9）轉儲導出文件中的DDL定義

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

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

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

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）預估導出文件的大小

可以預估導出文件的大小，而不執行真正的導出：

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

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

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

11）導入時過濾掉存儲參數

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

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

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

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

12）導入時表已存在的處理

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

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

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

以下替換導入單張表及其數據：

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

13）導出時覆蓋重名文件

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

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

14）導出帶有日期時間格式的文件名

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

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

以上導出的文件名格式形如CLES1901131806.DMP這樣的名稱。

15）通過數據庫鏈接導出導入

如果本地也建有數據庫，則可以通過網絡訪問遠程數據庫服務端。此時本地作為客戶端，需要在本地tnsnames中建立對應的網絡服務名，指向遠程數據庫服務端。之后通過如下命令創建數據庫鏈接對象：

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

這里創建了數據庫鏈接對象dl_mes，并設定以用戶名system和密碼mesHz2連接遠程數據庫，網絡服務名為mes_1。

查看創建的數據庫鏈接對象：

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

可以通過數據庫鏈接對象訪問遠程機器：

select * from scott.emp@dl_mes;

可以通過數據庫鏈接對象執行遠程數據庫的expdp導出，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將遠程數據庫對象導入到本地系統：

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

16）指定是否需要創建日志

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

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

17）會話交互模式

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

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

add_file：增加一個文件到導出目錄中，如：

Export>add_file=my_dir:dp02.dmp

stop_job：停止數據泵作業，如果設置stop_job=immediate將立即關閉數據泵作業，此時導出服務器進程終止。如果需要重新啟動該作業，則需要根據作業名使用attach命令重新開啟作業，如：

expdp system/mesHz2@mes attach=system.sys_export_full_01

start_job：重新恢復由于某種意外而停止的數據泵作業。

kill_job：殺掉客戶機進程和數據泵作業的服務器進程。

continue_client：退出交互方式，恢復正在運行的數據泵作業，實際的數據泵作業不受影響。

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

parallel：說明當前活動作業的工作者線程數量。

status：監視當前作業的狀態，參數設置為一個整數值秒數，如status=60，則每60秒會刷新一次作業狀態信息。