SQL*Loader是oracle提供的可以從多種平面檔案中向資料庫中載入資料的工具，使用sqlldr工具可以在很短的時間內向資料庫中載入大量的資料，像把製作好的excel表格匯入資料庫，可以說非常方便，相關的資料載入和解除安裝工具還有外部表，IMP/EXP，資料泵等，其實呢~ 關於SQL*Loader的學習多數時間是花在了琢磨sqlldr控制檔案的寫法上，下面來總結我的SQL*Loader學習過程和一些實驗案例。

一、sqlldr的命令幫助資訊
[oracle@cancer ~]$ sqlldr
SQL*Loader: Release 11.2.0.4.0 - Production on Tue Jan 26 16:54:30 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Usage: SQLLDR keyword=value [,keyword=value,...]
Valid Keywords:
--------------------------------------------------------------------------------------------------
userid ORACLE 使用者名稱/口令
control 控制檔名
log 日誌檔名
bad 錯誤檔名
data 資料檔名
discard 廢棄檔名
discardmax 允許廢棄的檔案的數目(預設全部)
skip 要跳過的邏輯記錄的數目(預設0)
load 要載入的邏輯記錄的數目(預設全部)
errors 允許的錯誤的數目 (預設50)
rows 常規路徑繫結陣列中或直接路徑儲存資料間的行數 (常規路徑預設64,直接路徑預設全部)
bindsize 常規路徑繫結陣列的大小(預設256000.單位位元組)
silent 執行過程中隱藏訊息(標題,反饋,錯誤,廢棄,分割槽)
direct 使用直接路徑(預設FALSE)
parfile 引數檔案：包含引數說明的檔案的名稱
parallel 執行並行載入(預設FALSE)
file 要從以下物件中分配區的檔案
--------------------------------------------------------------------------------------------------
skip_unusable_indexes 不允許/允許使用無用的索引(預設FALSE)
skip_index_maintenance 不維護索引，將受到影響的索引標記為失效(預設FALSE)
commit_discontinued 提交載入中斷時已載入的行(預設FALSE)
readsize 讀取緩衝區的大小 (預設1048576)
external_table 使用外部表進行加栽：NOT_USED,GENERATE_ONLY,EXECUTE (預設 NOT_USED)
columnarrayrows 直接路徑列陣列的行數(預設5000)
streamsize 直接路徑流緩衝區的大小(預設256000,單位位元組)
multithreading 在直接路徑中使用多執行緒
resumable 啟用或禁用當前的可恢復會話(預設FALSE)
resumable_name 有助於標識可恢復語句的文字字串
resumable_timeout RESUMABLE 的等待時間(以秒計)(預設 7200)
date_cache 日期轉換快取記憶體的大小(以條目計)(預設1000)
注意：SQLLDR的引數組合比較靈活，即可以直接寫值，也可以寫關鍵字=值。
例如：sqlldr scott/tiger foo 和 sqlldr control=foo userid=scott/tiger 兩種寫法均有效。
--------------------------------------------------------------------------------------------------

二、操作案例
1.簡單例子
新建一個ldr_case1.ctl的控制檔案，控制檔案的名稱和檔案型別可以任意指定，接著在控制檔案中寫入內容

LOAD DATA
INFILE *
INTO TABLE BONUS
FIELDS TERMINATED BY ","
(ENAME,JOB,SAL)
BEGINDATA
SMITH,CLEAK,3904
ALLEN,SALESMAN,2891
WARD,SALESMAN,3128
KING,PRESIDENT,2523

執行sqlldr命令

[oracle@cancer ~]$ sqlldr scott/tiger control=ldr_case1.ctl

SQL*Loader: Release 11.2.0.4.0 - Production on Wed Jan 27 08:23:36 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Commit point reached - logical record count 4

可以發現提示已經生成了4條資料，接著連線資料庫檢視一下內容

[oracle@cancer ~]$ sqlplus scott/tiger;

SQL> select * from bonus;

ENAME   JOB       SAL        COMM
------- --------- ---------- ----------
SMITH   CLEAK    3904
ALLEN   SALESMAN 2891
WARD SALESMAN 3128
KING    PRESIDENT 2523

發現查詢到的內容就是控制檔案中BEGINDATA中的資料，資料已經被成功載入。
提示：要插入的表必須在資料庫中已經存在，再使用sqlldr向其中載入資料

2.SQL*Loader的體系分析

LOAD DATA
-------------------------
INFILE *
INTO TABLE BONUS
FIELDS TERMINATED BY ","
(ENAME,JOB,SAL)
BEGINDATA
-------------------------
SMITH,CLEAK,3904
ALLEN,SALESMAN,2891
WARD,SALESMAN,3128
KING,PRESIDENT,2523

2.1控制檔案解析
①第一部分：
LOAD DATA是標準語法，控制檔案一般都以此開頭，LOAD DATA前還可指定 UNRECOVERABLE或RECOVERABLE來控制此次載入的資料是否可恢復，或者指定CONTINUE_LOAD，表示繼續載入，控制檔案的其他語句可以檢視官方文件。
②中間部分：
*INFILE:表示資料檔案位置，如果值為*，表示資料就在控制檔案中，本例中沒有單獨的資料檔案，對於大多數載入而言，都會將資料檔案與控制檔案分離。
*INTO TABLE tbl_name: tbl_name即資料要載入到的目標表，該表在你執行sqlldr命令之前必須已經建立。
*INTO前還有一些很有意思的引數需要說明：
a)INSERT:向表中插入資料，表必須為空，如果表非空的話，執行sqlldr命令時會報錯，預設就是INSERT引數。
b)APPEND:向表中追加資料，不管表中是否有資料。
c)REPLACE:替換表中資料，相當於先DELETE表中全部資料,然後再INSERT。
d)TRUNCATE:類似REPLACE,只不過這裡不是用DELETE方式刪除表中資料，而是透過TRUNCATE的方式刪除，然後再INSERT。
*FIELDS TERMINATED BY "，"：設定資料部分字串的分隔值，這裡設定為逗號（,）分隔，當然也可以換成其他任意可見字元，只要確定那是資料行中的分隔符就行。
*(ENAME,JOB,SAL):要插入的表的列名，這裡需要注意的是列名要與表中列名完全相同，列的順序可以與表中列順序不同，但是必須與資料部分的列一一對應?
*BEGINDATA:表示以下為待載入資料，僅當INFILE指定為*時有效。
③資料部分
在該案例中，是將資料部分與控制部分都放在控制檔案中，通常這部分是獨立存在於一個文字檔案中。如果是獨立的資料檔案，只需要將控制檔案中INFILE引數後面的*改為資料檔案的檔名即可。

2.2日誌檔案解析
在預設情況下，sqlldr命令在執行過程中，會自動產生一個與控制檔案同名的日誌檔案，副檔名為.log,日誌檔案中記錄了載入過程中的各項統計資訊，如一些初始化引數、讀取的記錄數、成功載入的記錄數、載入用時等。
前例中，執行完sqlldr命令之後，相同路徑下應該生成了一個ldr_case1.log檔案，直接以“記事本”工具開啟檢視，應該顯示如下內容：

[oracle@cancer ~]$ cat ldr_case1.log

SQL*Loader: Release 11.2.0.4.0 - Production on Wed Jan 27 08:23:36 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Control File:   ldr_case1.ctl
Data File:      ldr_case1.ctl
Bad File:     ldr_case1.bad
Discard File: none specified

(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array:     64 rows, maximum of 256000 bytes
Continuation:    none specified
Path used:      Conventional

Table BONUS, loaded from every logical record.
Insert option in effect for this table: INSERT

Column Name                  Position   Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
ENAME                               FIRST     *   ,       CHARACTER
JOB                                  NEXT     *   ,       CHARACTER
SAL                                  NEXT     *   ,       CHARACTER

Table BONUS:
4 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:                  49536 bytes(64 rows)
Read   buffer bytes: 1048576

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

Run began on Wed Jan 27 08:23:36 2016
Run ended on Wed Jan 27 08:23:36 2016

Elapsed time was:     00:00:00.39
CPU time was:         00:00:00.01

日誌檔案結構簡單，前面都是初始化的引數，中間及後半部分才是我們應該關注的，包括記錄的結構、操作的記錄數（含成功的和錯誤的）、花費的時間等，如在這個日誌檔案中加粗的部分顯示己經成功載入了 4條，共費時近40毫秒。

2.3錯誤檔案解析
sqlldr命令在執行過程中，不僅會產生日誌檔案，如果載入資料過程中由於資料不符合規範導致載入錯誤，還會產生一個同名的錯誤檔案，副檔名為bad(如果DBA 不顯式指定的話）。該檔案中記錄了出錯的資料。錯誤檔案中資料的格式與資料檔案完全相同，因此如果發現載入時出現錯誤檔案，根據日誌檔案分析出錯原因，解決後修改控制檔案中infile引數為錯誤檔案，然後重新執行sqlldr命令即可。

2.4廢棄檔案解析
除了日誌檔案和錯誤檔案，執行sqlldr命令時還有可能生成一個同名的廢棄檔案,副檔名為.dsc，在預設情況下不會有，必須在執行sqlldr命令時顯式指定廢棄檔案，並確實存在不符合匯入邏輯的記錄，裡面記錄了未被插入的資料

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

SQL*Loader的使用總結(一)

相關文章