SQL*Loader對不同檔案及格式的處理方法
1.大欄位(LOB型別)的匯入
LOB作為大欄位資料型別，是Oracle新增的資料型別，用來替代long和long raw型別，一般sqlldr操作中不會涉及大欄位型別的操作，如果你遇到了這種需求，一般分以下兩種情況處理：
1）資料儲存在資料檔案中
以Manager表為例，修改Remark欄位為lob型別

SQL> alter table manager drop column remark;
Table altered
SQL> alter table manager add remark clob;
Table altered

建立資料檔案和控制檔案如下：

--資料檔案
[oracle@cancer ~]$ cat ldr_case12_1.dat
10,SMITH,SALES MANAGER,This is SMITH.
He is a Sales Manager.|
11,ALLEN.W,TECH MANAGER,This is ALLEN.W.
He is a Tech Manager.|
16, BLAKE, HK MANAGER,"This is BLAKE. He is a Hr Manager. The jobs responsibilities are in the following:
1. Ensure the effective local implementation of corporate level HR initiatives and new programs.
2. Take initiatives in defining HR strategy on attracting, hiring, integrating, developing, managing......
3. Oversee standard recruiting and procedures to ensure the conpany's staffing requirements ......
4. Provide employees with fair and appropriate compensation and benefit, to ensure market competitiveness.
5. Develop, implement and oversee the training and development programs to upgrade the skills......"|

--控制檔案
[oracle@cancer ~]$ cat ldr_case12_1.ctl
LOAD DATA
INFILE ldr_case12_1.dat "str '|\n'"
TRUNCATE INTO TABLE MANAGER
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
(MGRNO,MNAME,JOB,REMARK char(100000))

注意這裡REMARK顯式指定char(100000)，因為Oracle預設所有輸入欄位都是char(255)，如不顯式指定型別和長度，一旦載入列的實際長度超出255,則資料載入時自動將該行忽略，並在對應的log日誌就會報錯：Field in data file exceeds maximum length.
執行sqlldr命令，並檢視結果

--執行sqlldr命令
[oracle@cancer ~]$ sqlldr scott/tiger control=ldr_case12_1.ctl
Commit point reached - logical record count 2
Commit point reached - logical record count 3

--檢視結果
SQL> select * from manager;
MGRNO MNAME           JOB             REMARK
------ --------------- --------------- ----------------------------------------------------------------------------------
   10 SMITH           SALES MANAGER   This is SMITH.
                                      He is a Sales Manager.
   11 ALLEN.W         TECH MANAGER    This is ALLEN.W.
                                      He is a Tech Manager.
   16 BLAKE           HK MANAGER      This is BLAKE. He is a Hr Manager. The jobs responsibilities are in the followin.....

資料成功載入。這種方式最關鍵的地方是必須保證REMARK列定義的長度大於資料檔案中文字塊的大小
2）資料儲存在獨立的檔案中
這種資料相對於第一種更加常見，相應處理也更簡單一些(跳過了換行符的處理)，sqlldr中提供了LOBFILE關鍵字，直接支援載入檔案到LOB型別中，這實在是幫了我們大忙，下面演示一下其用法。
首先在資料庫，建立一個新表：

CREATE TABLE LOBTBL
(
FILEOWNER   VARCHAR2(30),
FILENAME    VARCHAR2(200),
FILESIZE    NUMBER,
FILEDATA    CLOB,
CREATE_DATE DATE
);
Table created

表中共有5列，分別表示檔案屬主、檔名、檔案大小、檔案內容和檔案建立時間。建資料檔案，內容如下：

--資料檔案
[oracle@cancer sqlldr]$ cat ldr_case12_2.dat
2016-1-27 15:21 183   oracle /home/oracle/sqlldr/ldr_case11_1.dat
2016-1-27 15:22 150   oracle /home/oracle/sqlldr/ldr_case11_1.ctl
2016-1-27 15:22 1,714 oracle /home/oracle/sqlldr/ldr_case11_1.log
2016-1-27 16:05 166   oracle /home/oracle/sqlldr/ldr_case11_2.ctl
2016-1-27 16:13 136   oracle /home/oracle/sqlldr/ldr_case11_2.bad
2016-1-27 16:13 204   oracle /home/oracle/sqlldr/ldr_case11_2.dat
2016-1-27 16:13 1,696 oracle /home/oracle/sqlldr/ldr_case11_2.log
2016-1-27 16:35 120   oracle /home/oracle/sqlldr/ldr_case11_3.ctl
2016-1-27 16:55 188   oracle /home/oracle/sqlldr/ldr_case11_3.dat
2016-1-27 16:55 1,695 oracle /home/oracle/sqlldr/ldr_case11_3.log
2016-1-27 20:15 183   oracle /home/oracle/sqlldr/ldr_case11_4.dat
2016-1-27 20:33 126   oracle /home/oracle/sqlldr/ldr_case11_4.ctl
2016-1-27 20:33 3     oracle /home/oracle/sqlldr/ldr_case11_4.bad
2016-1-27 20:33 1,829 oracle /home/oracle/sqlldr/ldr_case11_4.log

--控制檔案
[oracle@cancer sqlldr]$ cat ldr_case12_2.ctl
LOAD DATA
INFILE ldr_case12_2.dat
TRUNCATE INTO TABLE LOBTBL
(
CREATE_DATE position(1:16) date 'yyyy-mm-dd hh24:mi',
FILESIZE position(*+2:23) "to_number(:FILESIZE,'99,999,999')",
FILEOWNER position(*+2:30),
FILENAME position(*+2:68) "substr(:FILENAME,instr(:FILENAME,'/',-1)+1)",
FILEDATA LOBFILE(FILENAME) TERMINATED BY EOF
)

這個控制檔案是之前介紹示例應用的集大成者，又有定長處理，又有函式轉換，唯一陌生的就是最後一行:LOBFILE(FILENAME)TERMINATED BY EOF,這就是前面提到的LOBFILE 關鍵字，只需要指定FILENAME列，其他都是固定格式，呼叫時直接按此指定即可。
執行sqlldr命令，並檢視結果

--執行sqlldr命令
[oracle@cancer sqlldr]$ sqlldr scott/tiger control=ldr_case12_2.ctl
Commit point reached - logical record count 14

--檢視結果
SQL> select * from lobtbl;
FILEOWNER FILENAME              FILESIZE FILEDATA                                                                   CREATE_DATE
--------- ------------------- ---------- -------------------------------------------------------------------------- -----------
oracle     ldr_case11_1.dat           183 10,SMITH,SALES MANAGER,This is SMITH.\nHe is a Sales Manager.              2016-01-27
                                          11,ALLEN.W,TECH M
oracle     ldr_case11_1.ctl           150 LOAD DATA                                                                  2016-01-27
                                          INFILE ldr_case11_1.dat
                                        TRUNCATE INTO TABLE MANAGER
                                          FIELDS TERMINAT
oracle     ldr_case11_1.log          1714                                                                            2016-01-27
                                        SQL*Loader: Release 11.2.0.4.0 - Production on Wed Jan 27 15:22:10 2016

                                        Cop
oracle     ldr_case11_2.ctl           166 LOAD DATA                                                                  2016-01-27
                                        INFILE ldr_case11_2.dat "fix 68"
                                          TRUNCATE INTO TABLE MANAGER
                                          (
                                        MGR
oracle     ldr_case11_2.bad           136                                                                            2016-01-27
                                        11 ALLEN.W TECH MANAGER THIS IS ALLEN.W
                                        HE IS A TECH MANAGER.
                                        16 BLAKE

因為篇幅原因不全部顯示，這裡注意的一點是在dat檔案中指定的路徑下必須存在相應的檔案，即/home/oracle/sqlldr/下存在dat中指定的檔案，否則載入會報錯
2.欄位無值導致載入報錯
在大多數情況下，專案在實際的實施過程中，能夠順利執行的機率通常都是與專案的複雜程度成反比，越是複雜的需求，實際實施過程中出現問題的機率就越高，因此在真正實施前，是否能夠充分考慮到意外出現的情況，也是考驗實施者技術實力的一個重要方面。
SQL*Loader工具的應用非常簡單，前面的多項示例能夠說明這一點，不過這不代表執行SQL*Loader就不會遇到錯誤，畢竟大多數情況下SQL*Loader中極重要的一環:資料檔案的生成過程並不由你掌控，因此編寫的控制檔案是否能夠適應資料檔案中資料的複雜多樣性，就是對DBA技術實力的一項綜合考驗了。比如某天你拿到了一個這樣的資料檔案：

--資料檔案
[oracle@cancer sqlldr]$ cat ldr_case13.dat
SMITH,CLEAR,3904
ALLEN,SALESMAN,
WARD,SALESMAN,3128
KING,PRESIDENT,2523

看起來和前面的某個例子很相似，根據此資料檔案建立控制檔案如下：

--控制檔案
[oracle@cancer sqlldr]$ cat ldr_case13.ctl
LOAD DATA
INFILE ldr_case13.dat
TRUNCATE INTO TABLE BONUS
FIELDS TERMINATED BY ","
(ENAME, JOB, SAL)

執行sqlldr命令，並檢視結果

--執行sqlldr命令
[oracle@cancer sqlldr]$ sqlldr scott/tiger control=ldr_case13.ctl
Commit point reached - logical record count 4

--檢視結果
SQL> select * from bonus;
ENAME      JOB                    SAL       COMM
---------- --------------- ---------- ----------
SMITH      CLEAR                 3904
WARD       SALESMAN              3128
KING       PRESIDENT             2523

很奇怪的發現明明4條記錄卻只匯入了3條，透過log日誌我們發現有如下提示：

Record 2: Rejected - Error on table BONUS, column SAL.
Column not found before end of logical record (use TRAILING NULLCOLS)

Table BONUS:
3 Rows successfully loaded.
1 Row not loaded due to data error

在此想首先闡述一個觀點：出現錯誤雖然不是什麼好訊息，但是錯誤本身並不可怕，最可怕的恰恰是沒有錯誤，程式看起來執行得好好的，四處査看也沒有提示資訊，但就是得不到想要的正確結果，這才是最頭痛的，你想處理都無從著手。
就本例中的錯誤資訊來說，sqlldr提示己經非常清楚：直到行結束也沒發現適當的列值。這是因為本例中資料檔案的第2行沒有提供適當的值(這一點都不稀奇，不管資料量龐大與否，DBA絕對不能期望資料檔案完全滿足要求，因此在編寫控制檔案時，也要考慮到對意外情況的處理)
針對這一錯誤，sqlldr甚至連解決方案也一併提供：使用TRAILING NULLCOLS。TRAILING NULLCOLS的作用是當某行沒有對應的列值時，sqlldr就會自動將其值陚為NULL，而不是報錯。
接下來我們嘗試修改控制檔案，增加的正是sqlldr的日誌檔案中提示的，檔案修改如下所示：

[oracle@cancer sqlldr]$ cat ldr_case13.ctl
--控制檔案
LOAD DATA
INFILE ldr_case13.dat
TRUNCATE INTO TABLE BONUS
FIELDS TERMINATED BY "," TRAILING NULLCOLS
(ENAME, JOB, SAL

這時候執行sqlldr命令時，結果就正常了

SQL> select * from bonus;
ENAME      JOB                    SAL       COMM
---------- --------------- ---------- ----------
SMITH      CLEAR                 3904
ALLEN      SALESMAN
WARD       SALESMAN              3128
KING       PRESIDENT             2523

透過這個例子，我們可以得到如下結論：

① 執行完操作後一定要驗證。就本例來說，從sqlldr命令的執行來看一切正常，如果不是到SQL*Plus環境中査看匯入的資料，恐怕都不知道有記錄未被成功匯入。
② —定要注意看日誌，sqlldr雖然算不上智慧，但是也不傻，有時候造成錯誤的原因只是它不知道怎麼辦好，不過日誌檔案中一定會留下處理痕跡，不管sqlldr命令執行是否成功，日誌檔案總是能告訴我們其執行的更多細節。

3.百萬級記錄資料的載入
前面己經講了很多示例，但資料量都較小，只能描述功能，實戰參考意義不大，因為在實際工作中應用sqlldr執行載入，多數情況資料量都達到一定級別，因此這裡構建了一個百萬記錄級的載入，看看實際載入效率如何，以及如何進行載入最佳化。
本次演示的第一小節也是資料UNLOAD的過程，只稍加修改即可以儲存為生成.csv 格式檔案的指令碼，希望也能對你有幫助。
1）生成百萬資料檔案
要用到的sql程式碼如下：

--第一條sql
[oracle@cancer sqlldr]$ cat getobject.sql
select a.owner||',"'||a.object_name||'",'||a.object_id||','||
to_char(a.created,'yyyy-mm-dd hh24:mi:ss')|| ','||a.status
from dba_objects a,(select rownum rn from dual connect by rownum<=20) b;

--第二條sql
[oracle@cancer sqlldr]$ cat call.sql
set echo off
set term off
set line 100 pages 0
set feedback off
set heading off
spool /home/oracle/sqlldr/ldr_object.csv
@/home/oracle/sqlldr/getobject.sql
spool off
set heading on
set feedback on
set term on
set echo on

登陸到SQL*Plus中執行call.sql

SQL> @/home/oracle/sqlldr/call.sql

然後用vim開啟生成的ldr_object.csv，執行:%s/\s\+$//來消除行尾的空格，生成的csv檔案大概在100m左右

2）初始化環境

--建立演示表
create table objects
(
owner        varchar2(30),
object_name varchar2(50),
object_id    number,
status       varchar2(10),
created      date
);
Table created.

--建立索引
SQL> create index idx_obj_owner_name on objects(owner,object_name);
Index created

3）執行匯入
建立控制檔案

--控制檔案
[oracle@cancer sqlldr]$ cat ldr_object.ctl
LOAD DATA
INFILE ldr_object.csv
TRUNCATE INTO TABLE OBJECTS
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
(
owner,
object_name,
object_id,
created date 'yyyy-mm-dd hh24:mi:ss',
status
)

這裡注意，對於CREATED列，我們指定了日期格式，並進行了轉換，這個格式一定要與資料檔案中日期格式相符，不然日期格式轉換時會報錯並導致資料載入失敗。
按照預設引數執行sqlldr，看看需要多長時間，同時指定ERRORS引數值為10，明確指定出現10次錯誤即中止載入。

--執行sqlldr命令
[oracle@cancer sqlldr]$ sqlldr scott/tiger control=ldr_object.ctl errors=10
Commit point reached - logical record count 64
Commit point reached - logical record count 128
Commit point reached - logical record count 192
....

等待載入完成後，我們觀察對應的日誌檔案:

Table OBJECTS:
1739580 Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.

Space allocated for bind array:                  82560 bytes(64 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:       1739580
Total logical records rejected:         0
Total logical records discarded:        0

Run began on Thu Jan 28 13:02:01 2016
Run ended on Thu Jan 28 13:12:23 2016

Elapsed time was:     00:10:21.66
CPU time was:         00:00:15.97

日誌檔案中得知，1739580條資料全部成功匯入，沒有失敗資料，共用時10分鐘左右。。。（因為用的是虛擬機器測試，效能可能稍微差一點）

4）匯入提速
sqlldr常規路徑匯入時預設一次載入64行，現在要載入的總行數已經達到百萬級，十位數顯然太小，我們首先嚐試修改該值，先直接在後面加個0好了，看看能對效率起到多大的提升：

--執行sqlldr命令
[oracle@cancer sqlldr]$ sqlldr scott/tiger control=ldr_object.ctl errors=10 rows=640
Commit point reached - logical record count 198
Commit point reached - logical record count 396
Commit point reached - logical record count 594
......

載入完成後，再次檢視下log日誌：

value used for ROWS parameter changed from 640 to 198

Table OBJECTS:
1739580 Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.

Space allocated for bind array:                 255420 bytes(198 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:       1739580
Total logical records rejected:         0
Total logical records discarded:        0

Run began on Thu Jan 28 13:23:50 2016
Run ended on Thu Jan 28 13:29:36 2016

Elapsed time was:     00:05:45.50
CPU time was:         00:00:08.73

log日誌中第一句value used for ROWS parameter changed from 640 to 198，該資訊是提示由於640行所佔用的空間己經超出了引數BINDSIZE的預設值，因此自動修改到最大可承受的198行，這說明BINDSIZE引數預設值偏小，不過即使是這樣，我們看到實際執行時間也被縮短到了5分鐘，提高了一倍以上，再進一步調整BINDSIZE引數值，預設為256K,我們將其修改為10M(1024KB*1024*10=10485760),同時將一次載入的行數提高到5000。

--執行sqlldr命令
[oracle@cancer sqlldr]$ sqlldr scott/tiger control=ldr_object.ctl errors=10 rows=5000 bindsize=10485760
specified value for readsize(1048576) less than bindsize(10485760)
Commit point reached - logical record count 5000
Commit point reached - logical record count 10000
Commit point reached - logical record count 15000
......

載入資料完成後，再次檢視日誌檔案，日誌資訊如下：

Space allocated for bind array:                6450000 bytes(5000 rows)
Read   buffer bytes:10485760

Total logical records skipped:          0
Total logical records read:       1739580
Total logical records rejected:         0
Total logical records discarded:        0

Run began on Thu Jan 28 13:37:15 2016
Run ended on Thu Jan 28 13:42:18 2016

Elapsed time was:     00:03:32.18
CPU time was:         00:00:04.25

由日誌資訊可以看到，時間被縮短到3分鐘多，幾乎又提高了一倍，而此時繫結陣列才佔用了6MB左右的空間，ROWS的引數值還可以繼續提高。不過因為我們這裡記錄量和數據量都稍小，繼續再提高這兩個引數的值，效率提升也不明顯了(僅針對這百萬記錄量的匯入而言，如果是千萬級資料量載入，縮短的時間應該還是很明顯的)。

5)使用Direct引數，讓速度更快一點
前面的引數都是基於常規路徑載入，下面透過直接路徑載入，所有引數預設，只開啟直接路徑載入引數：

--執行sqlldr命令
[oracle@cancer sqlldr]$ sqlldr scott/tiger control=ldr_object.ctl direct=true
Load completed - logical record count 1739580.

載入完成後，開啟log日誌，檢視節選的日誌資訊如下：

Table OBJECTS:
1739580 Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.

Date conversion cache disabled due to overflow (default size: 1000)

Bind array size not used in direct path.
Column array rows :    5000
Stream buffer bytes: 256000
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:       1739580
Total logical records rejected:         0
Total logical records discarded:        0
Total stream buffers loaded by SQL*Loader main thread:      427
Total stream buffers loaded by SQL*Loader load thread:      262

Run began on Thu Jan 28 13:59:32 2016
Run ended on Thu Jan 28 14:01:30 2016

Elapsed time was:     00:01:27.79
CPU time was:         00:00:03.02

發現載入時間已經降到近1分半鐘的時間，效能還是相當可以的

6）有沒有可能更快
直接路徑匯入可用的引數也有不少，不過我們這裡總資料量不大，因此實際能夠起到效率提升的不多，我準備主要從以下兩個引數著手：
①STREAMSIZE:直接路徑載入預設讀取全部記錄，因此不需要設定ROWS引數，讀取到的資料處理後存入流快取區，即STREAMSIZE引數。該引數預設值為256KB,這裡加大到10MB。
②DATE_CACHE:該引數指定一個轉換後日期格式的快取區，以條為單位，預設值1000條，即儲存1000條轉換後的日期格式，由於我們要匯入的資料中有日期列，因此加大該引數值到3000，以降低日期轉換操作帶來的開銷。
修改引數後執行命令最終形式如下所示：

--執行sqlldr命令
[oracle@cancer sqlldr]$ sqlldr scott/tiger control=ldr_object.ctl direct=true streamsize=10485760 date_cache=3000
Load completed - logical record count 1739580.

載入完成後，再次檢視命令，節選日誌資訊如下：

Table OBJECTS:
1739580 Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.

Date cache:
Max Size:      3000
Entries :      1252
Hits    :   1738328
Misses :         0

Bind array size not used in direct path.
Column array rows :    5000
Stream buffer bytes:10485760
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:       1739580
Total logical records rejected:         0
Total logical records discarded:        0
Total stream buffers loaded by SQL*Loader main thread:      427
Total stream buffers loaded by SQL*Loader load thread:        0

Run began on Thu Jan 28 14:12:47 2016
Run ended on Thu Jan 28 14:13:47 2016

Elapsed time was:     00:00:46.73
CPU time was:         00:00:01.71

170萬條的資料載入的時間大概在45秒左右，考慮到測試環境只是一臺低配的虛擬機器，這個效率也已經相當快了。

再引申說幾句，關於最佳化涉及層面太多，並非單單sqlldr調整好，效率就一定最高。上述演示建立在假設資料庫層面己經最優的情況下，透過合理配置sqlldr的引數來提高載入效率，但是不是能夠更快？我覺著回答是肯定的，最佳化並不是簡單地設定一個FAST=TRUE的引數，而是一個綜合考量下的結果。舉個例子，在前面例子中的控制檔案基本都沒有指定資料型別，這樣可能會導致產生隱式的型別轉換（也影響效率），如果全部顯式指定資料型別，並且改成定長格式，匯入效率還能得到一定提升。再比如說上述表中還建立了索引，如果單純希望資料載入效率提高，只需將表中的索引Disable，效率立刻又能提高一個數量級，但是Disable索引和約束是否符合你的業務需求，這就得看你的實際情況了。如果你理解得足夠深刻，就會發現所謂的調優，不過是將各種因素擺在一起，取一箇中間值，保持相互平衡罷了。

本文內容參考<塗抹Oracle-三思筆記>一書，該書是基於Windows，本文引用了該書的指令碼和結論的整理在Linux親自測試透過，並對一些小問題進行了處理

SQL*Loader的使用總結(四)

相關文章