Oracle SQL*Loader 完整語法與實戰指南：從入門到高效能並行載入

SQL*Loader 概覽

SQL*Loader (sqlldr) 是 Oracle 官方高效能資料載入工具，支援常規路徑 (Conventional Path，經 SQL 層)、直接路徑 (Direct Path，繞過 SQL 層直接寫資料塊)、並行直接路徑 (Parallel Direct Path，多會話同時載入同一表/分區)。適用於 TB 級資料遷移、數據倉庫每日載入、異構資料導入。

命令列參數完整表

控制檔語法詳解

-- 基本結構
LOAD DATA
{INFILE | INDDN} {'檔案路徑' | *}          -- 輸入資料來源 (* 表示控制檔內內嵌資料 BEGINDATA)
{BADFILE | BADDN} '檔案路徑'               -- 錯誤記錄檔
{DISCARDFILE | DISCARDDN} '檔案路徑'       -- 捨棄記錄檔
{DISCARDMAX | DISCARDMAX} n                -- 最大捨棄筆數
{INSERT | APPEND | REPLACE | TRUNCATE}     -- 載入模式 (見下表)
INTO TABLE [schema.]table_name
  [PARTITION (partition_name)]             -- 指定分區 (直接路徑並行必備)
  [WHEN 條件運算式]                        -- 篩選載入條件
  [FIELDS 規格]                            -- 欄位解析規則
  (
    column_name {POSITION(start:end) | TERMINATED BY '分隔符' | ENCLOSED BY '字元' ...}
      [datatype_spec]                      -- CHAR, INTEGER EXTERNAL, DATE "fmt", DECIMAL EXTERNAL 等
      [NULLIF 條件] [DEFAULTIF 條件]
  )

-- 多張表載入
INTO TABLE table2 ...
INTO TABLE table3 ...

載入模式對照

欄位解析常用規格

-- 固定寬度
col1 POSITION(1:10) CHAR(10)

-- 分隔符 (CSV)
col1 CHAR TERMINATED BY ','
col2 CHAR TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

-- 日期格式
birth_date DATE "YYYY-MM-DD"
create_ts TIMESTAMP "YYYY-MM-DD HH24:MI:SS.FF"

-- 數值
amount DECIMAL EXTERNAL TERMINATED BY ','
qty INTEGER EXTERNAL TERMINATED BY ','

-- 條件載入
WHEN (col1 != 'HEADER' AND col1 != 'TRAILER')  -- 跳過首尾行
NULLIF col1 = ''                               -- 空字串存 NULL
DEFAULTIF col1 = 'N/A'                         -- 特定值存預設值

實戰範例：CSV 匯入

資料檔 employees.csv

EMP_ID,EMP_NAME,DEPT_ID,HIRE_DATE,SALARY
1001,Alice,10,2020-01-15,50000
1002,Bob,20,2019-03-22,60000
1003,Carol,10,2021-07-01,55000

控制檔 load_emp.ctl

LOAD DATA
INFILE 'employees.csv'
BADFILE 'load_emp.bad'
DISCARDFILE 'load_emp.dsc'
DISCARDMAX 100
APPEND
INTO TABLE employees
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(
  emp_id      INTEGER EXTERNAL TERMINATED BY ',',
  emp_name    CHAR TERMINATED BY ',',
  dept_id     INTEGER EXTERNAL TERMINATED BY ',',
  hire_date   DATE "YYYY-MM-DD" TERMINATED BY ',',
  salary      DECIMAL EXTERNAL TERMINATED BY WHITESPACE
)

執行命令

sqlldr userid=scott/tiger@orcl \
  control=load_emp.ctl \
  log=load_emp.log \
  bad=load_emp.bad \
  discard=load_emp.dsc \
  errors=100000 \
  direct=true \
  rows=5000 \
  bindsize=10485760 \
  silent=(header,feedback)

高效能並行直接路徑載入

前置條件

• 目標表為分區表 (Range/Hash/List/Composite)，每個並行會話載入不同分區。
• 或表無索引/約束/觸發器 (或預先 DISABLE、UNUSABLE)，使用 SKIP_INDEX_MAINTENANCE=TRUE。
• 資料檔預先分割 (如 split -l 1000000 big.csv part_)，每個會話讀取一個檔案。
• 表設 NOLOGGING (非關鍵資料) 減少 Redo：ALTER TABLE employees NOLOGGING;，控制檔首行加 UNRECOVERABLE (需配合 DIRECT=TRUE)。

並行啟動範例

# 終端機 1
sqlldr userid=scott/tiger@orcl control=load_emp_p1.ctl direct=true parallel=true file=/data/ts_emp_01.dbf &

# 終端機 2
sqlldr userid=scott/tiger@orcl control=load_emp_p2.ctl direct=true parallel=true file=/data/ts_emp_02.dbf &

# 終端機 3
sqlldr userid=scott/tiger@orcl control=load_emp_p3.ctl direct=true parallel=true file=/data/ts_emp_03.dbf &

# 控制檔差異：INTO TABLE employees PARTITION (p1) ... INFILE 'part_aa.csv' 等

載入後必做

1. 重建索引：ALTER INDEX idx_emp_name REBUILD ONLINE PARALLEL 4; (並行重建)。
2. 啟用約束：ALTER TABLE employees ENABLE CONSTRAINT fk_dept;。
3. 收集統計資訊：DBMS_STATS.GATHER_TABLE_STATS('SCOTT','EMPLOYEES');。
4. 若用 NOLOGGING，執行完立即備份 (RMAN Level 0)，否則媒體故障會導致該段資料不可恢復。

常見問題與解決

進階技巧

• 內嵌資料：控制檔尾部 BEGINDATA 後直接放資料，適合小量測試。
• 動態產生控制檔：Shell/Python 依表結構自動產生 .ctl，減少人工錯誤。
• 外部表替代：10g+ CREATE TABLE ... ORGANIZATION EXTERNAL 可用 SQL 直接查詢/載入平面檔，彈性更高，適合 ETL 管線。
• Data Pump 對比：impdp 適用於 Oracle-to-Oracle 邏輯遷移 (含中繼資料)；sqlldr 適用於異構平面檔 (CSV/固定寬度) 載入。