10.4 Data Pump

Oracle Data Pump 為 Oracle 10g 開始推出的新功能， Data Pump 可以說是加強版的 Export 、 Import ，由 expdp 與 impdp 命令取代了原本的 exp 與 imp ，與傳統的 exp 、 imp 不同的是， exp 與 imp 是使用外部指令連接到資料庫進行匯出與匯入的動作，而 expdp 與 impdp 則是把外部指令轉換為資料庫內部的命令，使用的是 dbms_datapump 來進行匯出與匯入的動作，也就是說 expdp 與 impdp 是在資料庫內部運行的，當執行了 expdp 或 impdp ，會在資料庫產生一個 Data Pump Job ，經由這個 Job 來呼叫 dbms_datapump 從而完成匯出與匯入的動作，由於 Data Pump 是在資料庫內部運行的，並不會因為執行 expdp 、 impdp 的視窗被關閉後就會中斷，而 exp 與 imp 則是運行視窗被關閉後就會被中斷；另一個不同是，由於 Data Pump 是在資料庫內部運行，所以匯出的 Dump 檔案必須存放在資料庫主機上，而傳統的 exp 則是可以透過 tnsnames 將 Dump 檔匯出到 Client 主機上。

由於 Data Pump 所匯出的 Dump 檔案必須存放在資料庫主機上，所以在執行 Data Pump 之前必須於資料庫建立一個 Directory ， Directory 的用意是在資料庫建立一個名稱，這個名稱是要對應作業系統上的一個位置，之後資料庫就會知道要將 Dump 檔匯出到作業系統上的那一個位置， Directory 必須使用 sys 使用者建立，而 sys 也是 Directory 的擁有者，如果其他使用者也要使用的話，必須授權給它，例如於資料庫建立兩個 Directory ， expdir 與 impdir ，然後授權給 scott 與 hr 這兩個使用者:

SQL> create directory expdir as '/orabak'; SQL> create directory impdir as '/orabak2'; SQL> grant read,write on directory expdir to hr; SQL> grant read,write on directory expdir to scott; SQL> grant read,write on directory impdir to hr; SQL> grant read,write on directory impdir to scott;

如果不使用某個 Directory ，可以使用 drop directory 命令來刪除。

透過 dba_directories 可以查詢目前資料庫所建立的 Directory 與所對應的路徑:

Data Pump Export 使用的是 expdp 命令，執行 expdp help=y 會列出所有可以使用的參數，一般來說只要簡單的指定帳號密碼 、 Dump 檔案存放路徑 (Directory) 、 Dump 檔案的名稱以及所要匯出的資料範圍 (Full 、 Schemas 、 Tables) 就可以進行匯出的動作:

$ export ORACLE_SID=ORA11 $ expdp \'/ as sysdba\' full=y directory=expdir dumpfile=full.dmp logfile=full.log job_name=expfull   (匯出全資料庫) $ expdp \'/ as sysdba\' schemas=hr directory=expdir dumpfile=hr.dmp logfile=exphr.log   (匯出 hr 使用者所有物件) $ expdp hr/welcome1 tables=employees,departments directory=expdir dumpfile=exp.dmp logfile=exp.log   (匯出 employees 與 departments 兩個 Table)

Data Pump 會於資料庫內部建立一個 Job 來處理，透過 dba_datapump_jobs 可以查詢目前所進行的作業:

在執行 expdp 的時候可以帶入參數 job_name 自行為這個 job 命名，如果沒有使用 job_name 的話，系統會自行為這個 job 進行命名。

由於 Data Pump 是在資料庫裡面建立 Job 來執行，所以當 expdp 的執行介面被關閉時， Data Pump 的作業並不會因此停止，重新登入後可以使用 attach=<job_name> 來重新回到 Data Pump 執行的狀態，例如重回 expfull 這個 Job 的執行狀態:

$ export ORACLE_SID=ORA11 $ expdp \'/ as sysdba\' attach=expfull

在 attach 回去之後會進入到 Export> 介面，在此介面下可以對此 Job 進行狀態查詢 (status) 、 暫時停止 Job (stop_job) 、 重新開始 Job (start_job) 、 終止 Job (kill_job) 等動作:

expdp 一些比較常用的選項例如在 dumpfile 可以使用 %U ，這個選項可以自動切分並命名多個 Dump 檔案，例如 dumpfile=exp%U.dmp 就會自動產生 exp01.dmp 、 exp02.dmp 、exp03.dmp 等編號的 Dump 檔； parallel 可以設定多個 threads 來進行 Export 的動作； content 可以設定只匯出結構 (content=metadata_only) 或者是只匯出資料 (content=data_only) ； exclude 與 include ，可以指定只匯出或者是排除某些物件，例如只匯出 Procedure (include=procedure) 、不匯出 Index (exclude=index) ； parfile 用來指定參數檔， expdp 可以將所有要使用的參數設定為一個參數檔，然後用 parfile 來指定它就可以。

expdp 同樣也可以使用 query 條件式來匯出某些部分的資料，例如只匯出 employee_id < 200 的資料:

$ export ORACLE_SID=ORA11 $ expdp \'/ as sysdba\' tables=hr.employees directory=expdir query=\"where employee_id \<200\" dumpfile=emp.dmp logfile=emp.log

Data Pump Import 使用的是 impdp 命令，執行 impdp help=y 會顯示出所有可以使用的參數，同樣的只要簡單輸入帳號密碼， Dump 檔案名稱與位置就可以進行匯入的動作了。 impdp 與傳統的 imp 最大的不同是，傳統的 imp 要匯入 Table 的時候，必須先把相對應的使用者先建立好才可以進行匯入的動作，而 impdp 如果來源的 Dump 檔案是以 schema 層級匯出的話，如果使用者不存在，那麼 impdp 就會在匯入 Table 前自動建立使用者。 impdp 較常使用的參數除了上述 expdp 所提過的 parallel 、 exclude 、 include 、 job_name 、 parfile 之外，當匯入的 Table 已經存在的時候必須使用 table_exists_action 來告訴 impdp 必須進行何種動作， table_exists_action 可以為 append (接續著目前存在的資料進行匯入) 、 truncate (將 Table 的所有資料清空後重新匯入) 、 replace (將 Table 重新建立後再進行資料的匯入) 、 skip (跳過此 Table) ； remap_schema 用來將物件匯入到不同於來源的使用者； network_link 透過 db link 直接匯入資料，使用這個選項可以在不產生 Dump 檔案的情況下直接從來源資料庫將資料匯入。例如:

$ export ORACLE_SID=ORA11 $ impdp \'/ as sysdba\' directory=impdir dumpfile=emp%U.dmp logfile=imp.log   (匯入 emp&U.dmp 所有 dump 檔案的內容) $ impdp \'/ as sysdba\' directory=impdir dumpfile=hr.dmp remap_schema=hr:scott logfile=imphr.log   (將原本由 hr 使用者匯出的 hr.dmp 匯入到 scott 這個使用者底下) $ impdp hr/welcome1 directory=impdir dumpfile=emp.dmp table_exists_action=truncate logfile=impemp.log   (匯入 emp.dmp 所有內容，如果 Table 已經存在，則將資料全部清空再匯入) $ impdp \'/ as sysdba\' network_link=orcl schemas=scott logfile=imp_scott.log   (透過 db link 的方式匯入 scott 使用者所有物件)

使用 Data Pump 必須要注意的是，如果要終止掉正在執行的 Export 、 Import 作業，傳統的 exp 與 imp 只需要從作業系統執行 kill -9 指令就可以終止，但是 expdp 與 impdp 是在資料庫內部產生 Job 來執行，光是 kill 掉 expdp 、 impdp 是無法終止 Data Pump 的，這麼做反而會讓 Data Pump Job 殘留在資料庫裡面，正確的作法必須要使用 attach=<job_name> 進入到 Export> 或是 Import> 介面，然後執行 kill_job ，這樣才是完整的終止 Data Pump 作業。

Data Pump 不像傳統的 exp 、 imp 必須再特別設定環境變數 NLS_LANG ，因為 Data Pump 是在資料庫內部運行，所以不會有字元集不匹配的問題。由上述範例可以看到， Data Pump 可以使用的選項非常多，使用起來比起傳統的 exp 、 imp 更有彈性也更為便利，雖然現今版本的資料庫還保有 exp 與 imp 的命令，但是已經漸漸地很少使用它了，而是使用 expdp 與 impdp 進行取代。

10.3 Export 與 Import

Export 與 Import 是用來將 Oracle 資料庫物件匯出與匯入的一種工具，主要的用途是作為資料庫的備份，或者是用來做資料庫的移轉與升級，例如要將一套 Oracle 10g 的資料庫升級為 Oracle 11g ，那麼就可以在一台新的機器上安裝好 Oracle 11g ，然後將 Oracle 10g 的資料庫 Export 再 Import 到 11g ，這樣就完成升級的動作。

使用 Export 這項工具，只要安裝完 Oracle 軟體，執行命令 exp 就可以使用，執行 exp help=y 會顯示出所有 exp 可使用的參數:

exp 可以匯出的範圍包括整個資料庫 (Full) ，或者是針對某些使用者的所有物件進行匯出 (Owner) ，也可以只匯出 Table ；使用上只要 exp 後面加上登入資料庫的帳號密碼，再加上要匯出的標的與匯出的檔名，這樣就可以簡單完成 export 的動作，例如:

$ export NLS_LANG=AMERICAN_AMERICA.AL32UTF8 $ export ORACLE_SID=ORA11 $ exp system/manager full=y file=/orabak/exp_full.dmp log=/orabak/exp_full.log   (匯出資料庫所有物件) $ exp system/manager owner=scott file=/orabak/exp_scott.dmp log=/orabak/exp_scott.log   (匯出 scott 這個使用者的所有物件) $ exp \'/ as sysdba\' tables=hr.regions,hr.emp file=/orabak/hr.dmp log=/orabak/exp_hr.log   (使用 sys 登入資料庫來匯出 hr.regions 與 hr.emp 這兩個 Table)

除此之外， exp 還有一些比較常用的參數，例如 filesize ，用來限制一個 dmp 檔案的大小，早期受限於作業系統的限制，有檔案大小上限的問題，而要匯出的資料量又很大的時候，就必須使用 filesize 來限制與切分所匯出的 dmp 檔案，這邊要注意的是，使用 filesize 時，必須要列出所有 dmp 檔的檔名，否則 exp 作業會因為一個 dmp 檔達到 filesize 上限之後，不知道要使用什麼檔名來匯出下一個而停滯在那邊，例如限制 filesize 為 100MB ，那麼總共要匯出的資料有 300MB 的話，就必須指定三個 dmp 檔給它；使用 direct=y 參數可以直接從 Disk I/O 在 exp 過程當中不經過 cache ，可以加快 exp 的速度；如果只是要匯出 Table 的結構不匯出資料，那麼可以使用 rows=n 。例如使用 filesize 匯出 employees 這個 Table 的資料，以及匯出 department 這個 Table 的結構:

$ export NLS_LANG=AMERICAN_AMERICA.AL32UTF8 $ export ORACLE_SID=ORA11 $ exp scott/tiger tables=employees file=emp1.dmp,emp2.dmp,emp3.dmp filesize=100M direct=y log=emp.log $ exp hr/welcome1 tables=department rows=n file=dep.dmp log=dep.log

exp 也可以透過 Query 參數來下條件，只匯出 Table 某些部分的資料，例如要匯出 employee_id < 200 的資料，那麼就可以在 exp 的過程中使用 Query 條件式來過濾:

$ export NLS_LANG=AMERICAN_AMERICA.AL32UTF8 $ export ORACLE_SID=ORA11 $ exp hr/welcome1 tables=emp file=exp_emp.dmp query=\"where employee_id\<200\" log=exp_emp.log

exp 還有一個特點，就是可以透過 tnsnames 來連接資料庫， exp 作業不一定要在 DB Server 上執行，透過 tnsnames 的連接可以將 dmp 檔匯出到 client 端的 Server 上，例如透過一台 PC 主機將 orcl 資料庫的資料匯出:

D:\> set NLS_LANG=AMERICAN_AMERICA.AL32UTF8 D:\> exp scott/tiger@orcl tables=emp file=D:\orabak\exp_emp.dmp log=D:\orabak\exp_emp.log

最後要注意的是，執行 exp 之前，都必須要設定環境變數 NLS_LANG 與資料庫的字元集相同，否則有可能因字元集的問題導致匯出的資料不正確。

同樣的 Import 這項工具，執行命令為 imp ，使用 imp help=y 會列出所有可套用的參數:

使用 imp 只需給予 dmp 檔就可以將物件匯入資料庫，其中必須指定一種選項，匯入 dmp 所有的內容 (full=y) ，或者是指定匯入的 schema (fromuser, touser) ，例如:

$ export NLS_LANG=AMERICAN_AMERICA.AL32UTF8 $ export ORACLE_SID=ORA11 $ imp \'/ as sysdba\' full=y file=emp1.dmp,emp2.dmp,emp3.dmp log=imp_emp.log   (匯入所有 dmp 檔的內容) $ imp system/manager fromuser=hr touser=hr file=exp_hr.dmp log=imp_hr.log   (將 dmp 檔的內容，原本是從 hr 使用者匯出，現在也是匯入 hr 這個使用者底下) $ imp system/manager fromuser=scott touser=hr file=exp_emp.dmp log=imp_hr.log   (將 dmp 檔的內容，原本是從 scott 使用者匯出，現在匯入到 hr 這個使用者底下)

其餘較常使用的參數如 rows=n 不匯入資料； indexes=n 不匯入 index ； ignore=y 忽略 imp 過程中所有錯誤持續 import ，例如 import 過程中碰到資料重複的錯誤 Ora-0001 unique constraint violated ，要忽略掉這些重複的資料，其餘不重複的資料要繼續 import ，那麼就可以使用 ignore=y ，否則 imp 作業會因為發生錯誤而中斷開來； show=y 這個參數也蠻常使用，意思是由 dmp 檔案中產生出建立物件的 DDL ，不會真正的執行 import 的動作，例如從 exp_emp.dmp 中產生出 DDL 指令:

$ imp hr/hr file=exp_emp.dmp show=y log=imp_emp.log

同樣的在執行 imp 之前，也必須要設定環境變數 NLS_LANG 與資料庫字元集相同，否則也會因為字元集的不同導致匯入的資料發生錯誤。

由於 exp 、 imp 是將資料庫的物件匯出成一個 dmp 檔再匯入，所以這中間不會受到作業系統平台，或者是資料庫版本的限制，例如可以將一個在 AIX 平台上、 Oracle 9i 的 Table exp 出來，然後再將其 imp 到 Linux 平台、 Oracle 11g 的資料庫。在跨版本進行 exp 、 imp 時，需要注意的是，使用 exp 必須為比較低版本的，而 imp 可以是同版本或是更高版本的，例如由 Oracle 9i 移轉到 Oracle 10g ，那麼就可以使用 Oracle 9i 的 exp 將資料匯出，再使用 Oracle 10g 的 imp 將資料匯入；但是如果反過來，要將 Oracle 10g 的資料移轉到 Oracle 9i ，那麼就必須使用 Oracle 9i 的 exp ，透過 tnsnames 的方式連線到 Oracle 10g 進行 exp 的動作，然後才可以使用 Oracle 9i 的 imp 將資料匯入，因為高版本 exp 所產生出來的 dmp 檔是無法匯入到低版本的，所以這時候必須用低版本的 exp 來做匯出才行。

exp 、 imp 算是 Oracle 非常早期的工具，目前進行資料庫匯出匯入的動作大多都使用 Datapump ，已經很少使用 exp 、 imp 了。