Oracle SQL Loader 详解

适用版本：Oracle Database 10g / 11g / 12c / 19c / 23ai
文档版本：v1.0 / 2026-07

1. 概述

SQL*Loader 是数据加载工具[1]：

2. 控制文件

2.1 基本

LOAD DATA
INFILE 'sales.csv'
BADFILE 'sales.bad'
DISCARDFILE 'sales.dsc'
APPEND INTO TABLE sales
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
  id INTEGER EXTERNAL,
  amount DECIMAL EXTERNAL,
  sale_date DATE 'YYYY-MM-DD',
  region CHAR,
  status CONSTANT 'NEW'
)

2.2 加载模式

INSERT       - 插入（表必须空）
APPEND       - 追加
REPLACE      - 替换（DELETE 后 INSERT）
TRUNCATE     - TRUNCATE 后 INSERT

2.3 INFILE

INFILE 'sales.csv'
INFILE 'sales1.csv', 'sales2.csv'
INFILE *  -- 控制文件内
INFILE 'sales.dat' "str '|'"

3. 数据类型

3.1 基本

CHAR
DATE 'YYYY-MM-DD'
INTEGER EXTERNAL
DECIMAL EXTERNAL
FLOAT EXTERNAL
ZONED
BINARY
RAW
VARCHARC

3.2 示例

(
  id INTEGER EXTERNAL,
  name CHAR(100),
  salary DECIMAL EXTERNAL(10,2),
  hire_date DATE 'YYYY-MM-DD HH24:MI:SS',
  data RAW(1000)
)

4. 字段处理

4.1 分隔

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
FIELDS TERMINATED BY '|' 
FIELDS TERMINATED BY WHITESPACE

4.2 位置

(
  id POSITION(1:5) INTEGER EXTERNAL,
  name POSITION(6:25) CHAR,
  salary POSITION(26:35) DECIMAL EXTERNAL
)

4.3 NULL

-- NULL 条件
(
  salary DECIMAL EXTERNAL "NULLIF :salary = 'NULL'"
)

-- 缺失
TRAILING NULLCOLS

4.4 默认

status CONSTANT 'NEW',
create_date SYSDATE,
seq "seq_emp.NEXTVAL"

5. 转换

5.1 SQL 函数

(
  name CHAR "UPPER(:name)",
  salary DECIMAL EXTERNAL "TO_NUMBER(:salary, '999999.99')",
  email CHAR "LOWER(:email)"
)

5.2 复杂

(
  full_name CHAR "INITCAP(:full_name)",
  age INTEGER EXTERNAL "DECODE(:age, '', NULL, :age)"
)

6. 多表

6.1 INTO TABLE

LOAD DATA
INFILE 'data.csv'
APPEND INTO TABLE employees
WHEN (dept = '10')
FIELDS TERMINATED BY ','
( id, name, dept FILLER, dept_id CONSTANT 10 )

INTO TABLE employees
WHEN (dept = '20')
FIELDS TERMINATED BY ','
( id, name, dept FILLER, dept_id CONSTANT 20 )

6.2 FILLER

-- 跳过列
( id, junk FILLER, name )

7. 直接路径

7.1 优势

- 高速
- 跳过 SQL 处理
- 直接写入数据块
- 不触发触发器

7.2 命令

sqlldr scott/tiger control=sales.ctl direct=true

7.3 限制

- 索引维护
- 约束
- 触发器
- 锁

8. 命令

8.1 基本

sqlldr scott/tiger control=sales.ctl
sqlldr scott/tiger control=sales.ctl log=sales.log

8.2 参数

参数	说明
control	控制文件
log	日志
bad	错误文件
data	数据文件
discard	丢弃文件
direct	直接路径
skip	跳过行
load	加载行
errors	错误上限
rows	提交行
bindsize	绑定大小
readsize	读缓冲
parallel	并行

8.3 示例

sqlldr scott/tiger control=sales.ctl direct=true errors=100 \
  skip=1 rows=10000 bindsize=10485760 readsize=10485760

9. LOB

9.1 LOBFILE

(
  id INTEGER,
  doc LOBFILE(CONSTANT 'docs/' || :id || '.txt') TERMINATED BY EOF
)

9.2 BFILE

(
  id INTEGER,
  doc BFILE(CONSTANT 'DATA_DIR', :filename)
)

10. 复杂示例

10.1 CSV

LOAD DATA
INFILE 'employees.csv'
BADFILE 'employees.bad'
DISCARDFILE 'employees.dsc'
APPEND INTO TABLE employees
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
  id INTEGER EXTERNAL,
  name CHAR(100) "UPPER(:name)",
  email CHAR(200) "LOWER(:email)",
  phone CHAR(20),
  salary DECIMAL EXTERNAL(10,2),
  dept_id INTEGER EXTERNAL,
  hire_date DATE 'YYYY-MM-DD',
  status CONSTANT 'ACTIVE',
  created_at SYSTIMESTAMP,
  seq "seq_emp.NEXTVAL"
)

10.2 多表

LOAD DATA
INFILE 'data.csv'
TRUNCATE INTO TABLE emp_main
WHEN (rec_type = 'EMP')
FIELDS TERMINATED BY ','
(
  rec_type FILLER,
  id,
  name,
  salary
)

INTO TABLE dept_main
WHEN (rec_type = 'DEPT')
FIELDS TERMINATED BY ','
(
  rec_type FILLER POSITION(1),
  id,
  dept_name
)

11. 性能

11.1 直接路径

- 速度最快
- UNRECOVERABLE
- NOLOGGING

11.2 并行

sqlldr scott/tiger control=sales.ctl direct=true parallel=true

11.3 UNRECOVERABLE

OPTIONS (DIRECT=TRUE, UNRECOVERABLE)
LOAD DATA ...
UNRECOVERABLE
INTO TABLE sales ...

11.4 UNLOAD

- DISABLE INDEX
- 加载后重建

12. 日志

12.1 LOG

- 加载统计
- 错误
- 性能

12.2 BAD

- 错误数据
- 修正
- 重新加载

12.3 DISCARD

- 不满足 WHEN
- 检查

13. 应用场景

13.1 数据迁移

- CSV → Oracle
- 大量数据
- 速度

13.2 批量加载

- 日志文件
- 定期
- 直接路径

13.3 数据交换

- 系统间
- CSV
- 定期

详细见：Oracle 数据仓库 ETL 详解。

14. vs External Table

项	SQL*Loader	External Table
加载	推	拉
控制	控制文件	DDL
复杂	复杂	简单
并行	是	是
直接路径	是	是
灵活	中	高
推荐	旧	新

15. 常见坑与排错

15.1 ORA-01756

- 引号
- 检查

15.2 ORA-01858

- 日期格式
- DATE 'YYYY-MM-DD'

15.3 索引失效

- 直接路径
- 重建索引

15.4 锁

- 表锁
- 业务影响
- 时间窗

16. 最佳实践

直接路径：性能
并行：吞吐
UNRECOVERABLE：日志
NOLOGGING：归档
DISABLE INDEX：加载后
TRAILING NULLCOLS：NULL
SQL 函数：转换
多表：复杂
日志监控：质量
替代外部表：现代

17. 参考资料

[1] Oracle Database Utilities 19c, “SQL*Loader”
https://docs.oracle.com/en/database/oracle/oracle-database/19/sutil/