使用expdp(非本地)遠端導出資料

背景：

前段時間，需要從異地一個測試資料庫中将測試資料(一張表)導入本地庫，表資料量大約500萬，字段160多個，開始用了exp/imp方式，速度奇慢，不能忍，于是轉而使用expdp/impdp方式。

expdp/impd介紹：

從10g開始，除了傳統的exp/imp導入導出工具外，Oracle提供了expdp/impdp的資料泵導入導出工具。

從官方文檔上看(http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_overview.htm#SUTIL802)，Oracle資料泵由三部分組成：

>The command-line clients, expdp and impdp

>The DBMS_DATAPUMP PL/SQL package (also known as the Data Pump API)

>The DBMS_METADATA PL/SQL package (also known as the Metadata API)

The Data Pump clients, expdp and impdp, invoke the Data Pump Export utility and Data Pump Import utility, respectively.

資料泵用戶端(expdp/impdp)，分别會調用資料泵Data Pump Export/Import Utility應用工具。

The expdp and impdp clients use the procedures provided in the DBMS_DATAPUMP PL/SQL package to execute export and import commands, using the parameters entered at the command line. These parameters enable the exporting and importing of data and metadata for a complete database or for subsets of a database.

expdp/impdp用戶端使用由DBMS_DATAPUMP PL/SQL包提供的存儲過程來執行export/import指令，并且可以在指令行中添加參數，這些參數可以導入導出資料庫中的資料和中繼資料或其中的一部分。

When metadata is moved, Data Pump uses functionality provided by the DBMS_METADATA PL/SQL package. The DBMS_METADATA package provides a centralized facility for the extraction, manipulation, and re-creation of dictionary metadata.

如果需要導入導出中繼資料，資料泵會使用DBMS_METADATA PL/SQL包提供的函數。DBMS_METADATA包會提供便捷的方法，用于抽取、控制和重建資料字典中繼資料。

The DBMS_DATAPUMP and DBMS_METADATA PL/SQL packages can be used independently of the Data Pump clients.

DBMS_DATAPUMP和DBMS_METADATA的PL/SQL包可以獨立于資料泵用戶端使用。

All Data Pump Export and Import processing, including the reading and writing of dump files, is done on the system (server) selected by the specified database connect string. This means that for unprivileged users, the database administrator (DBA) must create directory objects for the Data Pump files that are read and written on that server file system. (For security reasons, DBAs must ensure that only approved users are allowed access to directory objects.) For privileged users, a default directory object is available. See "Default Locations for Dump, Log, and SQL Files" for more information about directory objects.

所有的資料泵導入導出過程，包括讀取和寫入的dump檔案，都在資料庫伺服器(server)上。這意味着對于非授權使用者，DBA必須為資料泵檔案建立伺服器端可讀寫的目錄對象，處于安全考慮，DBA必須確定隻有授權使用者可以通路這些目錄對象。對于授權使用者，可以使用預設的目錄對象。

總結：

資料泵工具expdp/impdp工具，執行時其實是調用的兩個PL/SQL包(一個對應資料，一個對應中繼資料)，讀取和寫入的dump檔案存儲于伺服器上directory目錄對應的檔案夾中。

問題來了，如果沒有伺服器的賬号，即使可以導出，但如何擷取到導出的dump呢？

現在的需求是，從本地伺服器使用expdp從遠端伺服器導出一張表的資料，然後使用impdp導入到本地庫，且沒有遠端伺服器的登入賬号。

一個遠端資料表的導入導出問題，當然這裡使用exp/imp完全可以做到，現在看看expdp/impdp如何做。

解決方案：通過dblink實作遠端資料的導入導出。

1. 本地庫建立dblink指向遠端庫。

create database link gf_local connect to username identified by password using 'tnsname';

2. 本地庫建立directory目錄對象。

create directory expdp_dir as '/home/oracle11g/expdp_dir/';

3. 本地庫建立資料泵導出參數檔案。

vi exp.par：

userid=username/password  //本地庫的使用者名密碼，可用上面建立的dblink和directory目錄。

directory=expdp_dir  //本地庫建立的directory目錄。

dumpfile=gf_expdp.dump  //dump檔案名。

logfile=gf_expdp.log  //日志檔案名。

tables=depkf15.t_cp  //要導出的表。

query='"where rownum<1000001"' //導出條件，前100萬行。

network_link=gf_local  //dblink名。

注意，這裡有個小問題值得注意，tables=depkf15.t_cp，如果待導出表和登入使用者不同，則這裡需要添加導出的對象schema(使用者)，否則會報錯。

4. 執行導出指令。

expdp parfile=exp.par

開始報錯了，提示：

ORA-31631:需要權限

ORA-39149:無法将授權使用者連結到非授權使用者

此時需要授予遠端資料庫使用者exp_full_database權限：

GRANT exp_full_database TO username;

接下來就是等待了，dump檔案會存儲至本地的expdp_dir指向路徑下。

1. expdp/impdp是10g以上提供的資料泵工具，運作時會調用兩個PL/SQL包，當然也可以直接運作這兩個PL/SQL包，沒試過，如果大家試過，也可以分享出來。

2. 導入資料庫賬戶需要有imp_full_database權限，導出資料庫賬戶需要有exp_full_database權限。

3. expdp比exp更優，還有一些地方，比如從help=y可以看到expdp有更多的參數可選，其中expdp有COMPRESSION壓縮參數可選，解釋如下：

Reduce the size of a dump file.

Valid keyword values are: ALL, DATA_ONLY, [METADATA_ONLY] and NONE.

可參考secooler的這篇文章(http://blog.itpub.net/519536/viewspace-630005/)，比對了四種參數的壓縮比。

4. 由于我的實驗中，遠端庫和本地庫之間網絡不是很好，是以實際效果上，exp和expdp都比較慢，也是因為用了dblink的方式，就沒再深入了，這裡隻是為了說明expdp/impdp如何實作遠端資料庫的導出。

後經同僚測試，一個800MB的檔案，使用exp導出大約用了1小時，expdp隻需要不到10分鐘，請注意，這裡未使用COMPRESSION參數。

有人說這是由于exp和expdp導出機制決定的，exp導出過程實際是由select執行的加載資料，放到buffer cache，再傳到export用戶端寫入dump檔案。expdp則是采用直接路徑模式，直接從磁盤讀取，寫入PGA，再傳到export用戶端寫入dump檔案。沒有經過buffer cache緩存就一定程度決定了他的導出速度。

5. 網上有人說“expdp/impdp是服務端程式，影響他的隻有磁盤IO”，從上面的介紹看，是不完全準确的，expdp/impdp是用戶端工具，執行時調用的是資料庫伺服器端的兩個PL/SQL包，上面的實驗也是通過dblink進行的遠端導入，并未在伺服器端使用。