資料泵匯出匯入

出於許多原因，我們期望從某個資料庫中提取大量資料以及關聯的物件定義，並且採用一種更容易的方式將這些資料載入到另一個資料庫中。備份就是其中一個重要原因，另外可能需要在生產與測試環境之間或聯機系統與資料倉儲之間轉移資料。資料泵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 hh24: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 hh24: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 hh24:mi:ss') and to_date('2015-03-31 23:59:59','yyyy-mm-dd hh24:mi:ss')",rmes.h_wip_tracking_t:"where in_line_time between to_date('2015-02-01 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2015-03-31 23:59:59','yyyy-mm-dd hh24: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秒會重新整理一次作業狀態資訊。