SQL*Loader 詳解

在 Oracle 資料庫中，我們通常在不同資料庫的表間記錄進行復制或遷移時會用以下幾種方法：

1. A 表的記錄匯出為一條條分號隔開的 insert 語句，然後執行插入到 B 表中
2. 建立資料庫間的 dblink，然後用 create table B as select * from  where ...，或 insert into B select * from  where ...
3. exp A 表，再 imp 到 B 表，exp 時可加查詢條件
4. 程式實現 select from A ..，然後 insert into B ...，也要分批提交
5. 再就是本篇要說到的 Sql Loader(sqlldr) 來匯入資料，效果比起逐條 insert 來很明顯

第 1 種方法在記錄多時是個噩夢，需三五百條的分批提交，否則客戶端會死掉，而且匯入過程很慢。如果要不產生 REDO 來提高 insert into 的效能，就要下面那樣做：

alter table B nologging;  

insert /* +APPEND */ into B(c1,c2) values(x,xx);  

insert /* +APPEND */ into B select * from A@dblink where .....;  

好啦，前面簡述了 Oracle 中資料匯入匯出的各種方法，一定還有更高明的。下面重點講講 Oracle  的 Sql Loader (sqlldr) 的用法。

在命令列下執行 Oracle  的 sqlldr 命令，可以看到它的詳細引數說明，要著重關注以下幾個引數：

userid -- Oracle 的 username/password[@servicename]
control -- 控制檔案，可能包含表的資料
-------------------------------------------------------------------------------------------------------
log -- 記錄匯入時的日誌檔案，預設為 控制檔案(去除副檔名).log
bad -- 壞資料檔案，預設為 控制檔案(去除副檔名).bad
data -- 資料檔案，一般在控制檔案中指定。用引數控制檔案中不指定資料檔案更適於自動操作
errors -- 允許的錯誤記錄數，可以用他來控制一條記錄都不能錯
rows -- 多少條記錄提交一次，預設為 64
skip -- 跳過的行數，比如匯出的資料檔案前面幾行是表頭或其他描述

還有更多的 sqlldr 的引數說明請參考：。

用例子來演示 sqlldr 的使用，有兩種使用方法：

1. 只使用一個控制檔案，在這個控制檔案中包含資料
2. 使用一個控制檔案(作為模板) 和一個資料檔案

一般為了利於模板和資料的分離，以及程式的不同分工會使用第二種方式，所以先來看這種用法。資料檔案可以是 CSV 檔案或者以其他分割符分隔的，資料檔案可以用 PL/SQL Developer 或者 Toad 匯出，也可以。另外，用 Toad 還能直接生成包含資料的控制檔案。

首先，假定有這麼一個表 users，並插入五條記錄：

create table users(      

user_id number,             --使用者 ID    

user_name varchar2(50),     --使用者名稱    

login_times number,         --登陸次數    

last_login              date--最後登入日期

);

"   ","USER_ID","USER_NAME","LOGIN_TIMES","LAST_LOGIN"  

"1"  ,"1","Unmi","3","2009-1-5 20:34:44"

"2"  ,"2","","5","2008-10-15"

"3"  ,"3","隔葉黃鶯","8","2009-1-2"

"4"  ,"4","Kypfos","",""

"5","5","不知秋","1","2008-12-23"  
2) 建立一個控制檔案 users.ctl，內容如下：

OPTIONS (skip=1,rows=128) -- sqlldr 命令顯示的選項可以寫到這裡邊來,skip=1 用來跳過資料中的第一行  

LOAD DATA  INFILE    "users_data.csv"--指定外部資料檔案，可以寫多個 INFILE "another_data_file.csv" 指定多個資料檔案--這裡還可以使用 BADFILE、DISCARDFILE 來指定壞資料和丟棄資料--的檔案，  

truncate   --操作型別，用 truncate table 來清除表中原有記錄

INTO   TABLE users -- 要插入記錄的表

Fields terminated     by","-- 資料中每行記錄用 "," 分隔

Optionally enclosed by '"' -- 資料中每個欄位用 '"' 框起，比如欄位中有 "," 分隔符時    

trailing nullcols --表的欄位沒有對應的值時允許為空

(    virtual_column FILLER,   --這是一個虛擬欄位，用來跳過由 PL/SQL Developer 生成的第一列序號  

user_id number,   --欄位可以指定型別，否則認為是 CHARACTER 型別, log 檔案中有顯示  

user_name,    

login_times,    

last_login     DATE"YYYY-MM-DD HH24:MI:SS"-- 指定接受日期的格式，相當用 to_date() 函式轉換

)  

說明：在操作型別 truncate 位置可用以下中的一值：
1) insert     --為預設方式，在資料裝載開始時要求表為空
2) append  --在表中追加新記錄
3) replace  --刪除舊記錄(用 delete from table 語句)，替換成新裝載的記錄
4) truncate --刪除舊記錄(用 truncate table 語句)，替換成新裝載的記錄

3) 執行命令：

sqlldr  control=users.ctl

在 dbservice 指示的資料庫的表 users 中記錄就和資料檔案中的一樣了。

執行完 sqlldr 後希望能留意一下生成的幾個檔案，如 users.log 日誌檔案、users.bad 壞資料檔案等。特別是要看看日誌檔案，從中可讓你更好的理解 Sql Loader，裡面有對控制檔案的解析、列出每個欄位的型別、載入記錄的統計、出錯原因等資訊。

第一種方式，只使用一個控制檔案在這個控制檔案中包含資料

1) 把 users_data.cvs 中的內容補到 users.ctl 中，並以 BEGINDATA 連線，還要把 INFILE "users_data.csv" 改為 INFILE *。同時為了更大化的說明問題，把資料處理了一下。此時，完整的 users.ctl 檔案內容是：

OPTIONS (skip=1,rows=128) -- sqlldr 命令顯示的選項可以寫到這裡邊來,skip=1 用來跳過資料中的第一行  

LOAD DATA  INFILE *    -- 因為資料同控制檔案在一起，所以用 * 表示

append      -- 這裡用了 append 來操作，在表 users 中附加記錄 

INTO TABLE users  when   LOGIN_TIMES<>'8'  -- 還可以用 when 子句選擇匯入符合條件的記錄

Fields terminated    by","

trailing nullcols  (    

virtual_column FILLER,   --跳過由 PL/SQL Developer 生成的第一列序號  

user_id   "user_seq.nextval", --這一列直接取序列的下一值，而不用資料中提供的值  

user_name   "'Hi '||upper(:user_name)",--,還能用SQL函式或運算對資料進行加工處理  

login_times terminated    by",", NULLIF(login_times='NULL') --可為列單獨指定分隔符  

last_login     DATE"YYYY-MM-DD HH24:MI:SS"NULLIF (last_login="NULL") -- 當欄位為"NULL"時就是 NULL)  

BEGINDATA   --資料從這裡開始   

,USER_ID,USER_NAME,LOGIN_TIMES,LAST_LOGIN  

1,1,Unmi,3,2009-1-5 20:34  

2,2,Fantasia,5,2008-10-15  

3,3,隔葉黃鶯,8,2009-1-2  

4,4,Kypfos,  NULL,NULL

5,5,不知秋,1,2008-12-23  

2) 執行一樣的命令：

sqlldr  control=users.ctl

比如，在控制檯會顯示這樣的資訊：

C:\>sqlldr  control=users.ctl

SQL*Loader: Release 9.2.0.1.0 - Production on 星期三 1月 7 22:26:25 2009

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

達到提交點，邏輯記錄計數4
達到提交點，邏輯記錄計數5

上面的控制檔案包含的內容比較複雜(演示目的)，請根據註釋理解每個引數的意義。還能由此發掘更多用法。

最後說下有關 SQL *Loader 的效能與併發操作

1) ROWS 的預設值為 64，你可以根據實際指定更合適的 ROWS 引數來指定每次提交記錄數。(體驗過在 PL/SQL Developer 中一次執行幾條條以上的 insert 語句的情形嗎？)

2）常規匯入可以透過使用 INSERT語句來匯入資料。Direct匯入可以跳過資料庫的相關邏輯(DIRECT=TRUE)，而直接將資料匯入到資料檔案中，可以提高匯入資料的效能。當然，在很多情況下，不能使用此引數(如果主鍵重複的話會使索引的狀態變成UNUSABLE!)。

3) 透過指定 UNRECOVERABLE選項，可以關閉資料庫的日誌(是否要 alter table table1 nologging 呢?)。這個選項只能和 direct 一起使用。

4) 對於超大資料檔案的匯入就要用併發操作了，即同時執行多個匯入任務.

  sqlldr   userid=/   control=result1.ctl   direct=true   parallel=true   
  sqlldr   userid=/   control=result2.ctl   direct=true   parallel=true   
  sqlldr   userid=/   control=result2.ctl   direct=true   parallel=true  

  當載入大量資料時（大約超過10GB），最好抑制日誌的產生：   
  
  SQL>ALTER   TABLE   RESULTXT   nologging; 
  
  這樣不產生REDO LOG，可以提高效率。然後在 CONTROL 檔案中 load data 上面加一行：unrecoverable，  此選項必須要與DIRECT共同應用。   
  
  在併發操作時，ORACLE聲稱可以達到每小時處理100GB資料的能力！其實，估計能到 1－10G 就算不錯了，開始可用結構 相同的檔案，但只有少量資料，成功後開始載入大量資料，這樣可以避免時間的浪費。

（注意：一般只能用ASCII碼形式，切記要轉換編碼，不然匯入資料為空）(ftp上傳csv檔案的傳輸型別選擇ascii)

參考：1.   -- 英文，Sql Loader 的官方使用說明，包含多種型別的  Demo
        2.   -- 列出了 sql loader 的選擇引數的中文說明
        3. 使用SQL Loader匯入大量資料，避免使用SQL頻繁寫庫 -- 一個簡單的例子，快帶了解 Sql Loader 的用法
        4. 
        5.  -- 還算名符其實。並講了如何用 SQL *Plus 的  spool 或 UTL_FILE 包生成資料檔案
        6.    -- 英文，控制檔案使用參考
        7. 學習oracle sql loader 的使用 
        8.   -- LOB 的內類是一個外部檔案，用 sql loader 匯入到資料庫
        9. SQLLDR直接載入幾個引數的測試
        10.