Oracle SQL*Loader 高效資料匯入實務：控制檔撰寫與命令列參數詳解

SQL*Loader (sqlldr) 大量資料載入完整指南

Oracle 官方高效能大量載入工具，支援固定寬度、分隔符、資料流等多種格式，繞過 SQL 層直接格式化資料區塊寫入。

控制檔案 (Control File) 結構範例

-- input.ctl
LOAD DATA
INFILE 'data.txt'               -- 資料檔路徑 (可多檔)
APPEND                          -- 模式: INSERT (表需空), APPEND, REPLACE, TRUNCATE
INTO TABLE target_table         -- 目標表
FIELDS TERMINATED BY '|'        -- 分隔符: '|', ',', X'09'(Tab), WHITESPACE
OPTIONALLY ENCLOSED BY '"'      -- 選擇性引號包裹
TRAILING NULLCOLS               -- 允許尾端欄位為 NULL
(
    col_id      CHAR(10),       -- 資料型別長度 (CHAR, DATE, INTEGER EXTERNAL, DECIMAL EXTERNAL...)
    col_name    CHAR(50),
    col_desc    CHAR(100),
    col_price   DECIMAL EXTERNAL(10,2),
    col_date    DATE "YYYY-MM-DD HH24:MI:SS", -- 日期格式轉換
    col_flag    POSITION(100:100) CHAR(1)     -- 固定寬度定位
)

資料檔範例 (data.txt)

1001|Product A|Description A|123.45|2023-01-15 10:30:00|Y
1002|Product B|Description B|67.89|2023-01-16 14:22:00|N

命令列執行參數

執行範例

sqlldr userid=scott/tiger@ORCL control=input.ctl data=data.txt log=import.log bad=import.bad errors=5000 direct=true

效能優化關鍵

1. 直接路徑 (DIRECT=TRUE)：繞過 SQL 引擎、無重做日誌 (NOLOGGING)、高水位線直接推進、支援平行載入。**鎖定整張表**，需維護窗口。
2. 停用索引/約束/觸發器：載入前 `DISABLE`，載入後 `REBUILD` / `ENABLE`，避免逐筆維護開銷。
3. 大量提交 (ROWS)：傳統路徑設定較大值 (如 10000) 減少 Commit 開銷。
4. 緩衝區調大：`BINDSIZE` / `READSIZE` 設 2MB~10MB 減少 I/O 呼叫。
5. 平行載入 (PARALLEL=TRUE)：配合分區表，多 session 同時載入不同分區，線性擴展吞吐。

常見錯誤排查

• ORA-01722: invalid number：資料格式不符合 `DECIMAL EXTERNAL` / `INTEGER EXTERNAL` 定義，檢查千分位符、負號、小數點。
• ORA-01841: (full) year must be between -4712 and +9999：日期格式遮罩與資料不符，確認 `DATE "format"` 雙引號大小寫。
• SQL*Loader-510: Physical record too long：單行超過 `READSIZE`，調大 `READSIZE` 或檢查換行符 (CRLF vs LF)。
• 記錄檔顯示 `Rejected` / `Discarded`：查看 `.bad` / `.dsc` 檔案內容定位問題行。