SQL*Loader(sqlldr)是 Oracle 官方提供的高效能大量資料載入工具,廣泛應用於資料倉庫初始化、ETL 階段性載入、跨平台遷移等場景。相較於傳統 INSERT 逐筆寫入,SQL*Loader 透過直接路徑載入可繞過 SGA、最小化 Redo/Undo 產生,效能提升可達數十倍。本文系統整理 SQL*Loader 核心架構、控制檔寫法、直接路徑模式原理與限制、常用參數與實務優化技巧。

| 檔案類型 | 說明 | 必要性 |
|---|---|---|
| 控制檔 | 定義資料格式、欄位對應、載入邏輯(WHEN 條件)、目標表、資料檔路徑等。最關鍵檔案。 | 必要 |
| 參數檔 | 將命令列參數(userid、control、log、bad、discard 等)集中管理,便於維護與排程。 | 選用 |
| 資料檔 | 來源平面檔(CSV、固定寬度、分隔符號等),可為多檔案。 | 必要 |
| 錯誤檔 | 載入失敗的記錄(資料型別不符、違反約束、格式錯誤等),副檔名慣用 .bad。 | 自動產生 |
| 捨棄檔 | 不符合控制檔 WHEN 條件被過濾的記錄,副檔名慣用 .dsc。 | 選用 |
| 日誌檔 | 記錄載入過程統計:讀取/載入/拒絕/捨棄筆數、錯誤訊息、耗時等,副檔名慣用 .log。 | 自動產生 |
LOAD DATA
INFILE 'data.csv' "str '|\n'" -- 資料檔與記錄分隔符
APPEND -- 載入模式:APPEND/INSERT/REPLACE/TRUNCATE
INTO TABLE target_table -- 目標表
FIELDS TERMINATED BY ',' -- 欄位分隔符
OPTIONALLY ENCLOSED BY '"' -- 選擇性引號包裹
TRAILING NULLCOLS -- 尾端缺漏欄位視為 NULL
(
col1 CHAR(10),
col2 DATE 'YYYY-MM-DD',
col3 DECIMAL EXTERNAL(10,2),
col4 "UPPER(:col4)", -- SQL 運算式轉換
col5 CONSTANT 'DEFAULT_VAL' -- 固定值
)
進階功能:WHEN 條件過濾、INTO TABLE 多表載入、BINDSIZE/READSIZE 調整緩衝區、DATE_CACHE 加速日期轉換。

| 特性 | 傳統路徑 | 直接路徑 |
|---|---|---|
| 機制 | 透過 SQL 層、SGA Buffer Cache、產生完整 Redo/Undo | 直接格式化資料區塊寫入 Segment 高水位線以上,繞過 SGA |
| Redo/Undo 量 | 完整產生 | 極少(僅 Data Dictionary Undo,無資料區塊 Undo) |
| 索引維護 | 逐筆維護 | 載入完成後一次重建(可 SORTED INDEXES 優化) |
| 觸發器/約束 | 全部觸發/檢查 | 僅支援 NOT NULL/UNIQUE/PK/CHECK;FK/Trigger 需停用或改用傳統路徑 |
| 並行度 | 單一 Session 序列化 | 支援 PARALLEL=TRUE 多 Session 並行載入不同分區 |
| 適用場景 | 小量資料、需觸發器/複雜約束、頻繁小批次 | 大量資料初始載入、分區表並行載入、資料倉庫批次作業 |
DIRECT=TRUEOPTIONS (DIRECT=TRUE)ALTER TABLE ... SHRINK SPACE 或重組。SELECT),會報 ORA-12838。INSERT /*+ APPEND */ ... SELECT ...、MERGE、OCI Direct Path API、SQL*Loader DIRECT=TRUE。BINDSIZE=10000000 READSIZE=10000000)。DATE_CACHE=10000 可大幅減少轉換開銷。PARALLEL=TRUE 搭配多個 Loader Session 分別載入不同分區,線性縮短載入時間。ALTER INDEX ... UNUSABLE、ALTER TABLE ... DISABLE CONSTRAINT,載入完成後 REBUILD/ENABLE,避免逐筆維護開銷。ALTER TABLE ... NOLOGGING 搭配直接路徑,進一步減少 Redo(需配合備份策略,因不可恢復)。| 錯誤現象 | 可能原因 | 解決方向 |
|---|---|---|
| ORA-01652: unable to extend temp segment | 直接路徑排序索引時暫存表空間不足 | 擴充 TEMP 表空間;或分批載入、調小 SORT_AREA_SIZE |
| ORA-01722: invalid number | 數值欄位含非數字字元 | 檢查資料檔;控制檔加 NULLIF/DECODE 清洗 |
| ORA-00001: unique constraint violated | 直接路徑載入違反 PK/UK | 預先停用約束,載入後驗證再啟用 |
| 載入速度未提升 | 未真正啟用直接路徑、或表有 Trigger/FK | 確認 Log 顯示 "Direct path load";停用 Trigger/FK |
SQL*Loader 是 Oracle 大量資料載入的基石工具。掌握控制檔寫法、理解直接路徑的原理與限制、善用並行與參數調校,能讓 TB 級資料載入從「數小時」壓縮至「數分鐘」。建議將常用載入模板參數化、腳本化,納入 ETL 標準作業流程,並配合 EXTERNAL TABLE、DBMS_CLOUD(雲端環境)等新技術彈性選擇最佳載入路徑。