Oracle使用資料泵expdp,impdp進行資料匯出匯入

視訊中的指令碼可以點選【下載例項】進行下載, 此指令碼在Windows和Linux下皆可以(適用於>=10g的oracle版本)

ORCALE10G提供了新的匯入匯出工具，資料泵。
Oracle官方對此的形容是：Oracle DataPump technology enables Very High-Speed movement of data and metadata from one database to another.其中Very High-Speed是亮點。
先說資料泵提供的主要特性（包括，但不限於）：

1. 支援並行處理匯入、匯出任務
2. 支援暫停和重啟動匯入、匯出任務
3. 支援通過Database Link的方式匯出或匯入遠端資料庫中的物件
4. 支援在匯入時通過Remap_schema、Remap_datafile、Remap_tablespace幾個引數實現匯入過程中自動修改物件屬主、 資料檔案或資料所在表空間。
5. 匯入/匯出時提供了非常細粒度的物件控制。通過Include、Exclude兩個引數，甚至可以詳細制定是否包含或不包含某個物件。

Warning:
1. 什麼是Directory物件
Directory物件是Oracle10g版本提供的一個新功能。他是一個 指向，指向了作業系統中的一個路徑。每個Directory都包含 Read,Write兩個許可權，可以通過Grant命令授權給指定的使用者或角色。擁有讀寫許可權的使用者就可以讀寫該Directory物件指定的作業系統路 徑下的檔案。
2. 除了使用network_link引數意外，expdp生成的檔案都是在伺服器上(Directory指定的位置)

如何呼叫
1. 命令列方式
最簡單的呼叫，但是寫的引數有限，建議使用引數檔案的方式。

2. 引數檔案方式
最常用的方式。通常需要先編寫一個引數檔案。指定匯出時需要的各種引數。然後以如下方式呼叫。
expdp user/pwd parfile=xxx.par
這個xxx.par即是我們編輯的引數檔案。注意，在這個命令列後面，同樣可以再跟別的引數， 甚至是在par引數檔案中指定過的引數。如果執行命令中附加 的引數與引數檔案中的引數有重複，最終採用哪個引數，會以引數最後出現的位置而定。如：expdp user/pwd parfile=xxx.par logfile=a.log，如果在引數檔案中也指定了logfile，這裡會以命令列中的logfile為準；如：expdp user/pwd logfile=a.log parfile=xxx.par，而這個，則會以引數檔案中的為準，因為parfile=xxx.par寫在命令列的後面。

3. 互動方式
Data Pump匯入匯出任務支援停止，重啟等狀態操作。如使用者執行匯入或者匯出任務，執行了一半時，使用Crtl+C中斷了任務（或其他原因導致的中斷），此時 任務並不是被取消，而是被轉移到後臺。可以再次使用expdp/impdp命令，附加attach引數的方式重新連線到中斷的任務中，並選擇後續的操作。 這就是互動方式。
Warning : 什麼是attach引數，每執行一個匯入，或者匯出，在命令的第一行，會有以下資訊：Starting “BAM”.”SYS_EXPORT_SCHEMA_01″:  bam/******** parfile=expdp_tbs.par，這個SYS_EXPORT_SCHEMA_01就是我們的attach引數。
-bash-3.00$ expdp bam/bam parfile=expdp_tbs.par
Export: Release 10.2.0.4.0 – 64bit Production on Friday, 13 August, 2010 16:35:18
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting “BAM”.”SYS_EXPORT_SCHEMA_01″:  bam/******** parfile=expdp_tbs.par
如果想使用互動方式，可以使用如：expdp attach SYS_EXPORT_SCHEMA_01 進入到互動模式

操作模式
1. 全庫模式
匯入或者匯出整個資料庫，對應impdp/expdp命令中的full引數，只有擁有dba或者 exp_full_database和imp_full_database許可權的使用者才能執行。
2. Schema模式
匯出或匯入Schema下的自有物件，對應impdp/expdp命令中的Schema引數，這是預設的操作模式。 如果擁有dba或者 exp_full_database和imp_full_database許可權的使用者執行的話，就可以匯出或匯入多個Schema中的物件。
3. 表模式
匯出指定的表或者表分割槽（如果有分割槽的話）以及依賴該表的物件（如該表的索引，約束等，不過前提是這些物件在同一個 Schema中，或者執行的使用者有相應的許可權）。對應impdp/expdp命令中的Table引數。
4. 表空間模式
匯出指定的表空間中的內容。對應impdp/expdp中的Tablespaces引數，這種模式類似於表模式和 Schema模式的補充。
5. 傳輸表空間模式
對應impdp/expdp中的Transport_tablespaces引數。這種模式與前面幾種模式最顯著的區 別是生成的Dump檔案中並不包含具 體的邏輯資料，而只匯出相關物件的後設資料(即物件的定義，可以理解成表的建立語句)，邏輯資料仍然在表空間的資料檔案中，匯出時需要將後設資料和資料檔案同 時複製到目標端伺服器。
這種匯出方式效率很高，時間開銷主要是花在複製資料檔案產生的I/O上。expdp執行傳輸表空間模式的匯出，使用者必須 擁有 exp_full_database角色或者DBA角色。而通過傳輸表空間模式匯入時，使用者必須擁有imp_full_database角色或者DBA角 色。

過濾資料
過濾資料主要依賴於Query和Sample兩個引數。其中Sample引數主要針對expdp匯出功能。
1. Query
與exp命令中的Query功能類似，不過Expdp中，該引數功能得到了增強，控制的粒度更細。Expdp中的Query也是指定類 似where語句來限定記錄。語法如下：
Query = [Schema.][Table_name:] Query_clause
預設 情況如果不指定Schema.table_name,則Query_clause針對所有匯出的表有效，或者你可以為每一個表指定不同的 Query_clause，如：匯出a表中所有id<5的記錄，匯出b表中所有name=’a’的記錄，則Query的引數應該如下：
Query=A:”Where id<5″,B:”Where name=’a’”
如果Where條件前沒有指定Schema名或者表名的話，預設就是針對當前所有要被 匯出的表。如：Query=Where id <5
Warning: 建議把Query引數放入到引數檔案中使用，以避免轉義符帶來的麻煩。
2. Sample
該引數用來指定匯出資料的百分比，可指定的值的範圍從0.000001到99.999999，語法如下：
Sample=[[Schema_name.]Table_name:]sample_percent
指 定該引數以後，EXPDP匯出將自動控制匯出的記錄量，如匯出A表中50%的記錄，設定的Sample引數如下：
Sample=A:50
Warning: Sample_percent指定的值只是一個參考值，EXPDP會根據資料量算出一個近似值。

過濾物件
過濾物件主要依賴於Include和Exclude兩個引數。這兩個引數作用正好相反，在這兩個引數中，可以指定你知道的任何物件 型別（如：Package、Procedure、Table等等）或者物件名稱（支援萬用字元）
1. Exclude 反規則
指定不被包含的物件型別或者物件名稱。指定了該引數以後，指定的物件型別對應的所有物件都不會被匯入或匯出。 如果被排除的物件有依賴的物件，那麼其依賴的 物件也不會被匯入或匯出。如：通過Exclude引數指定不匯出表物件的話，不僅指定的表不會被匯出，連這些表關聯的Index、Check等都不會被匯出。Warning: 建議把Exclude引數放入到引數檔案中使用，以避免轉義符帶來的麻煩。
2. Include 正規則
與Exclude正好相反。指定包含的物件型別或者物件名稱。
Warning: 由於兩個引數功能正好相反，因此在執行匯入或匯出命令時，兩個引數不能同時使用，否則Oracle也不知道你想要幹什麼啦。

exclude/include引數用法：
EXCLUDE=[object_type]:[name_clause],[object_type]:[name_clause]  -->排出特定物件
INCLUDE=[object_type]:[name_clause],[object_type]:[name_clause]  -->包含特定物件
object_type子句用於指定物件的型別，如table,sequence,view,procedure,package等等
name_clause子句可以為SQL表示式用於過濾特定的物件名字。它由SQL操作符以及物件名(可使用萬用字元)來過濾指定物件型別中的特定物件。
當未指定name_clause而僅僅指定object_type則所有該型別的物件都將被過濾或篩選。多個[object_type]:[name_clause]中間以逗號分割。
示例：
expdp <other_parameters> SCHEMAS=scott EXCLUDE=SEQUENCE,TABLE:"IN ('EMP','DEPT')"
impdp <other_parameters> SCHEMAS=scott INCLUDE=PACKAGE,FUNCTION,PROCEDURE,TABLE:"='EMP'"
常用的過濾SQL表示式
EXCLUDE=SEQUENCE,VIEW                          --過濾所有的SEQUENCE,VIEW
EXCLUDE=TABLE:"IN ('EMP','DEPT')"               --過濾表物件EMP,DEPT
EXCLUDE=SEQUENCE,VIEW,TABLE:"IN ('EMP','DEPT')" --過濾所有的SEQUENCE,VIEW以及表物件EMP,DEPT
EXCLUDE=INDEX:"= 'INDX_NAME'"                   --過濾指定的索引物件INDX_NAME
INCLUDE=PROCEDURE:"LIKE 'PROC_U%'"              --包含以PROC_U開頭的所有儲存過程(_ 符號代表任意單個字元)
INCLUDE=TABLE:"> 'E' "                          --包含大於字元E的所有表物件
其它常用操作符 NOT IN, NOT LIKE, <, != 等等
直接將過濾操作符封裝到引數檔案中，如下面的例子
Parameter file:exp_scott.par
DIRECTORY = dump_scott
DUMPFILE = exp_scott_%U.dmp
LOGFILE = exp_scott.log
SCHEMAS = scott
PARALLEL= 2
EXCLUDE = TABLE:"IN ('EMP', 'DEPT')" 
expdp system/manager parfile=exp.par                                                           
命令列下轉義符的處理
Windows平臺:
D:\> expdp system/manager DIRECTORY=my_dir DUMPFILE=exp_tab.dmp LOGFILE=exp_tab.log SCHEMAS=scott
INCLUDE=TABLE:\"IN ('EMP', 'DEPT')\"
在Windows平臺下，需要物件雙引號進行轉義，使用轉義符\
Unix平臺:
在未使用parfile檔案的情形下，所有的符號都需要進行轉義，包括括號，雙引號，單引號等
% expdp system/manager DIRECTORY=my_dir DUMPFILE=exp_tab.dmp LOGFILE=exp_tab.log SCHEMAS=scott
INCLUDE=TABLE:\"IN \(\'EMP\', \'DEP\'\)\"
exclude/include常見的錯誤
任意需要轉義的字元如果未轉義或轉義錯誤，都會產生ORA錯誤。下面給出幾種常見的ORA錯誤。
ORA-39001: invalid argument value
ORA-39071: Value for INCLUDE is badly formed.
ORA-00936: missing expression
ORA-39001: invalid argument value
ORA-39071: Value for EXCLUDE is badly formed.
ORA-00904: “DEPT”: invalid identifier
ORA-39001: invalid argument value
ORA-39041: Filter “INCLUDE” either identifies all object types or no object types.
ORA-39001: invalid argument value
ORA-39041: Filter “EXCLUDE” either identifies all object types or no object types
ORA-39001: invalid argument value
ORA-39038: Object path “USER” is not supported for TABLE jobs.

高階過濾
在匯出/匯入的時候，我們常常有這樣的需求，只想匯出/匯入表結構，或者只想匯出/匯入資料。幸運的是資料泵也提供了該功能。使用 Content引數。該引數有三個屬性
1) ALL : 匯出/匯入物件定義和資料，該引數的預設值就是ALL
2) DATA_ONLY : 只匯出/匯入資料。
3) METADATA_ONLY : 只匯出/匯入物件定義。
Warning: 有一點值得注意的時，在執行匯出的時候，如果使用了高階過濾，如只匯出了資料，那麼匯入時，需要確保資料定義已經存在。否則資料都變成沒有主子了。如果數 據定義已經存在，匯入時最好指定data_only，否則會觸發ORA-39151錯誤，因為物件已經存在了。
過濾已經存在的資料
我們知道，匯入的表物件在目標庫中已經存在，並且目標端沒有建立資料完整性約束條件(RI)來檢驗資料的話，就有可能造 成資料被重複匯入。資料泵提供了一 個新的引數Table_exists_action,可以一定程度上降低重複資料的產生。該引數用來控制如果要匯入的表物件存在，執行什麼操作。有以下幾 個引數值：
1) SKIP : 跳過該表，繼續處理下一個物件。該引數預設就是SKIP。值得注意的是，如果你同時指定了CONTENT引數為Data_only的話，SKIP引數無 效，預設為APPEND。
2) APPEND : 向現有的表中新增資料。
3) TRUNCATE : TRUNCATE當前表，然後再新增記錄。使用這個引數需要謹慎，除非確認當前表中的資料確實無用。否則可能造成資料丟失。
4) REPLACE : 刪除並重建表物件，然後再向其中新增資料。值得注意的是，如果同時指定了CONTENT引數為Data_only的話，REPLACE引數無效。

重定義表的Schema或表空間
我們還可能會遇到這樣的需求，把A使用者的物件轉移到B使用者，或者更換資料的表空間。資料泵通過 Remap_Schema和Remap_tablespace引數實現了該功能。
1) REMAP_SCHEMA : 重定義物件所屬Schema
該引數的作用類似IMP中的Fromuser+Touser,支援多個Schema的轉換，語法如下：
REMAP_SCHEMA=Source_schema:Target_schema[,Source_schema:Target_schema]
如 把A的物件轉換到C使用者，將C轉換到D使用者。Remap_schema=a:b,c:d
Warning: 不能在同一個IMPDP命令中指定remap_schema=a:b,a:c.
2) REMAP_TABLESPACE : 重定義物件所在的表空間。
該引數用來重對映匯入物件儲存的表空間，支援同時對多個表空間進行 轉換，相互間用逗號分割。語法如下：
REMAP_TABLESPACE=Source_tablespace:Target_tablespace[,Source_tablespace:Target_tablespace]
Warning: 如果使用Remap_tablespace引數，則要保證匯入的使用者對目標表空間有讀寫許可權。

優化匯入/匯出效率
對於大資料量來說，我們不得不考慮效率問題。資料泵對效率也提出了更高的要求。甚至官方的描述就是Oracle Data Pump technology enables Very High-Speed movement of data and metadata from one database to another.這裡的Very High-Speed依賴我們的parallel引數。
所 有的優化操作都會有三種結果：變得更好、沒有變化、變得更差。Parallel引數也是這樣，並不是指定一個大於1的引數，效能就會有提升。
1) 對於匯出的parallel
對於匯出來說，由於dump檔案只能由一個執行緒進行操作（包 括I/O處理），因此如果輸出的DUMP檔案只有一個，即使你指定再多的並行，實際工作仍然是 一個，而且還會觸發ORA-39095錯誤。因此，建議設定該引數小於或等於生成的DUMP檔案數量。那麼，如何控制生成的DUMP檔案數量呢？
EXPDP 命令提供了一個FILESIZE引數，用來指定單個DUMP檔案的最大容量，要有效的利用parallel引數，filesize引數必不可少。
舉 例：某使用者物件佔用了4G左右的空間，實際匯出後的DUMP檔案約為3G，我們嘗試在匯出該使用者時指定並行度為4，設定單個檔案不超過500M，則語法如 下：
$ expdp user/pwd directory=dump_file dumpfile=expdp_20100820_%U.dmp logfile=expdp_20100820.log filesize=500M parallel=4
2) 對於匯入的parallel
對於匯入來說，使用parallel引數則要簡單的多，我認為匯入更能體現parallel引數的優勢。 引數設定為幾，則認為同時將幾張表的內容匯入到庫中。
舉例：某dmp檔案中包含了200張表，我們嘗試在匯入該DMP檔案時指定並行度為10，則 語法如下：
$ impdp user/pwd directory=dump_file dumpfile=expdp_20100820.dmp logfile=impdp_20100820.log parallel=10

如何進入互動模式
在這裡，我正在執行匯入，我想進入互動模式，檢視匯入的狀態。進入互動模式有兩種方式，操作步驟如下：
i 使用Ctrl+C退出當前模式
ii 在命令列模式下，執行Expdp/Impdp命令，同時指定attach引數連線到當前正在制定的匯入/匯出任務。如：
expdp bam/bam attach=SYS_IMPORT_FULL_01
Warning:如果沒有指定Attach引數，則預設進入當前正在執行的 任務。不過如果當前沒有正在指定的任務，而且也沒有給Attach賦值，那麼就會報Ora-31626錯誤。
當命令列進入互動模式後，會顯示如下 介面：
Export>
7) 互動模式的操作
在互動模式中，支援下面幾種操作。
i 檢視JOB的執行狀態
Export> status
ii 回退到命令列
Export> continue_client
iii 增加並行
Export> parallel=4
Warning: 在使用匯出時，不能直接指定parallel引數，否則可能會遇到ORA-39095錯誤，因為如果要並行匯出，則必須指定多個匯出檔案，這裡的並行匯出 是指，多個執行緒同時工作，同時從資料庫中匯出多個dmp檔案來。
在Oracle Database Utilities中有如下解釋：
Because each active worker process or I/O server process writes exclusively to one file at a time, an insufficient number of files can have adverse effects. Some of the worker processes will be idle while waiting for files, thereby degrading the overall performance of the job. More importantly, if any member of a cooperating group of parallel I/O server processes cannot obtain a file for output, then the export operation will be stopped with an ORA-39095 error. Both situations can be corrected by attaching to the job using the Data Pump Export utility, adding more files using the ADD_FILE command while in interactive mode, and in the case of a stopped job, restarting the job.
You can supply multiple file_name specifications as a comma-delimited list or in separate DUMPFILE parameter specifications. If no extension is given for the filename, then Export uses the default file extension of .dmp. The filenames can contain a substitution variable (%U), which implies that multiple files may be generated. The substitution variable is expanded in the resulting filenames into a 2-digit, fixed-width, incrementing integer starting at 01 and ending at 99. If a file specification contains two substitution variables, both are incremented at the same time. For example, exp%Uaa%U.dmp would resolve to exp01aa01.dmp, exp02aa02.dmp, and so forth.
iv 停止JOB
Export> stop_job
v 啟動JOB
Export> start_job
vi 殺掉JOB
Export> kill_job
vii 退出互動模式
Export> exit_client
viii 指定檔案大小
Export> filesize=1G
ix 幫助
Export> Help

JOB_NAME
指定要匯出作用的名稱,預設為SYS_XXX
JOB_NAME=jobname_string
SELECT * FROM DBA_DATAPUMP_JOBS;--檢視存在的job

NETWORK_LINK匯出檔案到本地
遠端：
          IP:192.168.10.55,OS:RHEL4.8 64bit,Oracle:10.2.0.5 64bit
          被匯出使用者:monitor
本地：
       ip: 172.*，WINDOWS XP,oracle 10.2.0.4。
      本地tns :cheniwo
      本地配置的聯絡到遠端的tns:lobom55
1)確定本地可訪問遠端
F:\oracle\product\10.2.0\db_2\network\admin\sqlnet.ora
已使用 TNSNAMES 介面卡來解析別名
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP
(HOST = 192.168.10.55)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = lobomb)))
OK (40 毫秒)
2）在本地建立dblink到遠端
F:\Documents and Settings\Administrator>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on 星期四 9月 6 14:25:44 2012
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
連線到:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create database link dl_monitor connect to monitor identified by monitor using 'lobom55';
資料庫連結已建立。
SQL> select sysdate from dual@dl_monitor;
SYSDATE
--------------
06-9月 -12
3）資料泵匯出
F:\Documents and Settings\Administrator>expdp network_link=dl_monitor dumpfile=n
etwork_monitor.dmp
/*--------------------------------------------------
說明：network_link是你建立的到遠端的database link
下邊輸入的使用者名稱是你本地資料庫的認證
dumpfile 將放在data_pump_dir目錄定義的本地目錄下
-------------------------------*/
Export: Release 10.2.0.4.0 - Production on 星期四, 06 9月, 2012 14:40:17
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
使用者名稱: / as sysdba
連線到: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
啟動 "SYS"."SYS_EXPORT_SCHEMA_01":  /******** AS SYSDBA network_link=dl_monitor
dumpfile=network_monitor.dmp
正在使用 BLOCKS 方法進行估計...
處理物件型別 SCHEMA_EXPORT/TABLE/TABLE_DATA
使用 BLOCKS 方法的總估計: 81.68 MB
處理物件型別 SCHEMA_EXPORT/USER
.............................省略若干行.............................
處理物件型別 SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_ST
ATISTICS
已成功載入/解除安裝了主表 "SYS"."SYS_EXPORT_SCHEMA_01"
******************************************************************************
SYS.SYS_EXPORT_SCHEMA_01 的轉儲檔案集為:
  F:\ORACLE\PRODUCT\10.2.0\ADMIN\CHENIWO\DPDUMP\NETWORK_MONITOR.DMP
作業 "SYS"."SYS_EXPORT_SCHEMA_01" 已於 14:51:41 成功完成
4）檢查結果：
SQL> set head off
SQL> select * from dba_directories where directory_name='DATA_PUMP_DIR';
SYS  DATA_PUMP_DIR   F:\oracle\product\10.2.0\admin\cheniwo\dpdump\
F:\Documents and Settings\Administrator>cd F:\oracle\product\10.2.0\admin\cheniwo\dpdump
F:\oracle\product\10.2.0\admin\cheniwo\dpdump>dir /w
驅動器 F 中的卷是 新加捲
卷的序列號是 88F0-A421
F:\oracle\product\10.2.0\admin\cheniwo\dpdump 的目錄
[.]                   [..]                  cheniwo.sql
CHENIWO_DP.DMP        dp.log                EXPDAT.DMP
export.log            FULL_CHENIWO.DMP      import.log
NETWORK_MONITOR.DMP   TABLES_CHENIWO.DMP    TABLES_CHENIWO2.DMP
TS_CHENIWO.DMP        USER_CHENIWO.DMP
              12 個檔案    128,977,860 位元組
               2 個目錄 155,032,698,880 可用位元組