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 了。

10.2 SQL Loader

Oracle SQL Loader 是一個能夠把資料從檔案匯入到資料庫的一種工具，檔案格式可以是純文字檔 (Text File) ，或者是以逗號分隔 (CSV 格式) 的檔案…等，只要是檔案中的資料欄位可以與資料庫 Table 的欄位互相匹配，基本上都可以使用 SQL Loader 將資料從檔案匯入到 Oracle 資料庫。

執行 SQL Loader 使用的是 sqlldr 這個命令，只要執行 sqlldr 就會提示如何使用:

實際上只要簡單執行 sqlldr <帳號>/<密碼>@<db> sqldr.ctl 就可以了，意思是使用 sqlldr 登入資料庫並執行 sqldr.ctl 裡面的內容， sqldr.ctl 是 SQL Loader 的 control file ，其實就是所謂的設定檔，裡面要定義資料來源、欄位的 mapping …等資訊，這個檔案是我們執行 sqlldr 之前要自己編寫的，整個 SQL Loader 的核心其實就在於如何撰寫這個 control file 。

SQL Loader 使用的 control file 是有固定格式的，必須遵循這四個項目依序來編寫:

• Options 子句: 設定 SQL Loader 的 Options ，例如容錯筆數、是否使用 Direct Load 、 是否使用 Parallel …等， Options 子句可以添加或者是不使用。

• Load Data 子句: 設定資料來源。

• Load Method 子句: 設定匯入資料的方式:

• INSERT : 直接使用 insert 命令匯入資料，這個方式必須在 Table 沒有任何資料的時候才可以使用。

• APPEND : 使用 insert 匯入資料，且資料都是 new rows ，在原本的資料之後新增到 Table 中。

• REPLACE : 使用 delete 的方式刪除 Table 所有的資料，然後再 insert 資料進去。

• TRUNCATE : 使用 truncate 的方式清除 Table 所有的資料，然後再 insert 資料進去。

• Into Table 子句 : 用來設定資料來源與資料庫 Table 欄位的 mapping 。

了解了 control file 的格式之後，就可以來編寫它，例如要將一個 CSV 格式的資料匯入 emp 這個 Table 當中，那麼就可以編輯一個名為 emp.ctl 的 control file 如下 :

options(errors=100,direct=true) load data infile 'emp.csv' truncate into table emp fields terminated by ',' optionally enclosed by '"' trailing nullcols (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE DATE "YYYY-MM-DD hh24:mi:ss", JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)

首先 options 設定容錯筆數為 100 ，並且執行 direct load ； Load Data 子句設定來源檔案為 emp.csv ； Load Method 子句設定為 TRUNCATE ； into table emp 表示將資料匯入 emp 這個 Table ，以下將針對欄位做 mapping :

• fields terminated by ',' : 表示資料來源的欄位是以逗號分隔。

• optionally enclosed by '"' : 表示資料來源當中，使用雙引號 “ 框起來的資料視為同一個欄位的資料，例如一筆資料為 “ Taipei,Taiwan “ ，雖然是逗號分隔，但是在雙引號裡面， “ Taipei,Taiwan “ 代表是同一個欄位的資料，如果沒有用雙引號，那麼在逗號分隔下，就會區分為兩個欄位 Taipei 與 Taiwan 。

• trailing nullcols : 代表這個欄位如果沒資料的話，那麼就視為是 null 。

最後將 Table 所有的欄位列出來，每個逗號分隔的資料剛好可以跟 Table 的這些欄位做匹配，這邊要注意的是， SQL Loader 讀取資料的時候，預設的資料型態都是 char ，如果 Table 所定義的欄位型態不是 char 的話，那麼就要在這邊進行設定，例如 HIRE_DATE 這個欄位的型態為 DATE ，所以在這邊就要特別註記為 DATE ，且格式為 "YYYY-MM-DD hh24:mi:ss" 。

編寫好 control file 之後，那麼就可以使用 sqlldr 將資料匯入資料庫了:

其中 bad 是設定 bad file ，表示如果有資料格式錯誤無法匯入，那麼就將這筆資料寫入 bad file 裡面； discard 表示 discard file ，雖然資料格式正確，但是條件不符，那麼就把這筆資料寫入 discard file ，例如匯入的資料當中與 Primary Key 的欄位出現重複的資料，那麼這些重複的資料就無法匯入，並且寫入 discard file 當中。

sqlldr 執行完畢後，可以檢視 log file 確認是否匯入的過程當中有產生錯誤，以及匯入的資料量是否正確:

除此之外，如果資料是屬於固定長度格式的 Text File ，也可以設定 control file 如下:

options(errors=100,direct=true) load data infile 'sales.txt' truncate into table sales (SALE_NO    POSITION(1:5)  INTEGER EXTERNAL,  SNAME      POSITION(6:13)  CHAR,   TITLE      POSITION(14:33)  CHAR,   DEPT_NO    POSITION(34:35)  CHAR,  SALARY     POSITION(36:44)  INTEGER EXTERNAL,  COMMISION  POSITION(45:49)  INTEGER EXTERNAL,  MANAGER_ID POSITION(50:54)  INTEGER EXTERNAL    APP_DATE   POSITION(56:65)  DATE "YYYY/MM/DD" )

不同於逗號分隔，無須設定 “fields terminated by” 等參數，而改由 POSITION 參數設定，直接做欄位的 mapping ， SALE_NO POSITION(1:5) 表示 Text File 的第一位到第五位的資料屬於欄位 SALE_NO ； SNAME POSITION(6:13) 表示第六位到第十三位的資料屬於欄位 SNAME … 以此類推。

SQL Loader 的 control file 所可以註記的資料型態共三種 :

• CHAR : 代表文字型態，為預設的資料型態，預設的長度為 255 ， Table 定義的 char 或 varchar2 ，對 SQL Loader 來說都是以 char 表示。如果 Table 的資料長度大於 255 (例如 1000) ，那麼就要特別註記長度為 char(1000) 。

• INTEGER EXTERNAL (或 DECIMAL EXTERNAL) : 代表數字型態， Table 定義的數字型態如 Number 、 INT …等，對 SQL Loader 來說都是以 INTEGER EXTERNAL (或 DECIMAL EXTERNAL) 來表示。

• DATE : 代表日期型態，與 Table 所表示的 DATE 意義相同。

實務上最常用的大概就是匯入固定長度的 Text File 與 CSV 格式的檔案了，雖然 SQL Loader 的 control file 不是那麼好編寫，但只要熟悉之後，這會是一個很好用的工具。