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

一、sqlldr的命令幫助資訊

[oracle@wjq ~]$ sqlldr

SQL*Loader: Release 11.2.0.4.0 - Production on Tue Oct 31 11:46:27 2017

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

Usage: SQLLDR keyword=value [,keyword=value,...]

Valid Keywords:

    userid -- ORACLE username/password           #Oracle使用者名稱和口令

   control -- control file name                  #控制檔名

       log -- log file name                      #日誌檔名

       bad -- bad file name                      #錯誤檔名

      data -- data file name                     #資料檔名

   discard -- discard file name                  #廢棄檔名

discardmax -- number of discards to allow (Default all)        #允許廢棄的全部數目(預設全部)

      skip -- number of logical records to skip(Default 0)     #要跳過的邏輯記錄數目(預設0)

      load -- number of logical records to load(Default all)   #要載入的邏輯記錄數目(預設全部)

    errors -- number of errors to allow (Default 50)           #允許錯誤的數目（預設50）

      rows -- number of rows in conventional path bind array or between direct path data saves

               (Default: Conventional path 64, Direct path all)#常規路徑繫結陣列中或直接路徑儲存資料間的行數 (常規路徑預設64,直接路徑預設全部)

  bindsize -- size of conventional path bind array in bytes  (Default 256000)#常規路徑繫結資料的大小(預設256000位元組)

    silent -- suppress messages during run (header,feedback,errors,discards,partitions)#執行過程中隱藏的資訊(標題，反饋，錯誤，廢棄，分割槽)

    direct -- use direct path                      (Default FALSE)#使用直接路徑(預設FALSE)

   parfile -- parameter file: name of file that contains parameter specifications#引數檔案，包括引數說明檔案的名稱

  parallel -- do parallel load                     (Default FALSE)#執行並行載入(預設FALSE)

      file -- file to allocate extents from      #要從以下檔案中分配區的檔案

skip_unusable_indexes -- disallow/allow unusable indexes or index partitions  (Default FALSE)#不允許/允許使用無用的索引(預設FALSE)

skip_index_maintenance -- do not maintain indexes, mark affected indexes as unusable  (Default FALSE)#不維護索引，對受到影響的索引標記為失效(預設FALSE)

commit_discontinued -- commit loaded rows when load is discontinued  (Default FALSE)#提交載入中斷時已載入的行(預設FALSE)

  readsize -- size of read buffer                  (Default 1048576)#讀取緩衝區的大小(預設1048576位元組)

external_table -- use external table for load; NOT_USED, GENERATE_ONLY, EXECUTE  (Default NOT_USED)#使用外部表進行載入:NOT_USED, GENERATE_ONLY, EXECUTE(預設NOT_USED)

columnarrayrows -- number of rows for direct path column array  (Default 5000)#直接路徑列陣列的行數(預設5000)

streamsize -- size of direct path stream buffer in bytes  (Default 256000)#直接路徑流緩衝區的大小(預設256000)

multithreading -- use multithreading in direct path  #在直接路徑中使用多執行緒

 resumable -- enable or disable resumable for current session  (Default FALSE)#啟用或禁用當前可恢復會話(預設FALSE)

resumable_name -- text string to help identify resumable statement#有助於標識可恢復語句的文字字串

resumable_timeout -- wait time (in seconds) for RESUMABLE  (Default 7200)#RESUMABLE的等待時間(預設7200秒)

date_cache -- size (in entries) of date conversion cache  (Default 1000)#日期轉換高速緩衝區大小（以條目計）(預設1000條)

no_index_errors -- abort load on any index errors  (Default FALSE)

PLEASE NOTE: Command-line parameters may be specified either by

position or by keywords.  An example of the former case is 'sqlldr

scott/tiger foo'; an example of the latter is 'sqlldr control=foo

userid=scott/tiger'.  One may specify parameters by position before

but not after parameters specified by keywords.  For example,

'sqlldr scott/tiger control=foo logfile=log' is allowed, but

'sqlldr scott/tiger control=foo log' is not, even though the

position of the parameter 'log' is correct.

注意：

SQLLDR的引數組合比較靈活，即可以直接寫值，也可以寫關鍵字=值。例如：sqlldr scott/tiger foo 和 sqlldr control=foo userid=scott/tiger 兩種寫法均有效。

二、使用案例

1.1簡單例子

新建一個wjq_test1.ctl的控制檔案，控制檔案的名稱和檔案型別可以任意指定，接著在控制檔案中寫入內容

SCOTT@seiang11g>create table tb_loader as select * from bonus;

Table created.

SCOTT@seiang11g>desc tb_loader

 Name                             Null?    Type

 -------------------------------- -------- ------------------------------------

 ENAME                                     VARCHAR2(10)

 JOB                                       VARCHAR2(9)

 SAL                                       NUMBER

 COMM                                      NUMBER

控制檔案內容如下：

[oracle@wjq SQL*Loader]$ vim wjq_test1.ctl
LOAD DATA
INFILE *
INTO TABLE tb_loader
FIELDS TERMINATED BY ","
(ENAME,JOB,SAL)
BEGINDATA
SMITH,CLEAK,3904
ALLEN,SALESMAN,2891 
WARD,SALESMAN,3128
KING,PRESIDENT,2523

執行sqlldr命令

[oracle@wjq SQL*Loader]$ sqlldr scott/tiger control=/u01/app/oracle/SQL*Loader/wjq_test1.ctl

SQL*Loader: Release 11.2.0.4.0 - Production on Tue Oct 31 14:43:12 2017

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

Commit point reached - logical record count 4

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

SCOTT@seiang11g>select * from tb_loader;

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 tb_loader

FIELDS TERMINATED BY ","

(ENAME,JOB,SAL)

BEGINDATA

------------------------------------

SMITH,CLEAK,3904

ALLEN,SALESMAN,2891 

WARD,SALESMAN,3128

KING,PRESIDENT,2523

1.2控制檔案解析

①第一部分：

LOAD DATA是標準語法，控制檔案一般都以此開頭，LOAD DATA前還可指定 UNRECOVERABLE或RECOVERABLE來控制此次載入的資料是否可恢復，或者指定CONTINUE_LOAD，表示繼續載入，控制檔案的其他語句可以檢視官方文件。

②中間部分：
*INFILE:表示資料檔案位置，如果值為*，表示資料就在控制檔案中，本例中沒有單獨的資料檔案，對於大多數載入而言，都會將資料檔案與控制檔案分離。
*INTO TABLE tbl_name: tbl_name即資料要載入到的目標表，該表在你執行sqlldr命令之前必須已經建立。
*INTO前還有一些很有意思的引數需要說明：
  *INSERT:向表中插入資料，表必須為空，如果表非空的話，執行sqlldr命令時會報錯，預設就是INSERT引數。
  *APPEND:向表中追加資料，不管表中是否有資料。
  *REPLACE:替換表中資料，相當於先DELETE表中全部資料,然後再INSERT。
  *TRUNCATE:類似REPLACE,只不過這裡不是用DELETE方式刪除表中資料，而是透過TRUNCATE的方式刪除，然後再INSERT。
*FIELDS TERMINATED BY "，"：設定資料部分字串的分隔值，這裡設定為逗 號（,）分隔，當然也可以換成其他任意可見字元，只要確定那是資料行中的分隔符就行。
*(ENAME,JOB,SAL):要插入的表的列名，這裡需要注意的是列名要與表中列名完全相同，列的順序可以與表中列順序不同，但是必須與資料部分的列一一對應?
*BEGINDATA:表示以下為待載入資料，僅當INFILE指定為*時有效。

③資料部分
在該案例中，是將資料部分與控制部分都放在控制檔案中，通常這部分是獨立存在於一個文字檔案中。如果是獨立的資料檔案，只需要將控制檔案中INFILE引數後面的*改為資料檔案的檔名即可。

1.3日誌檔案解析

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

[oracle@wjq SQL*Loader]$ cat wjq_test1.log

SQL*Loader: Release 11.2.0.4.0 - Production on Tue Oct 31 14:43:12 2017

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

Control File:   /u01/app/oracle/SQL*Loader/wjq_test1.ctl
Data File:      /u01/app/oracle/SQL*Loader/wjq_test1.ctl
  Bad File:     /u01/app/oracle/SQL*Loader/wjq_test1.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 TB_LOADER, 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 TB_LOADER:
  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 Tue Oct 31 14:43:12 2017
Run ended on Tue Oct 31 14:43:12 2017

Elapsed time was:     00:00:00.03
CPU time was:         00:00:00.01

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

1.4錯誤檔案解析

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

1.5廢棄檔案解析

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