DB / Oracle / SQL 开发

Oracle SQL Loader 详解

2026.09.06

Oracle SQL Loader 详解

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

1. 概述

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

详细见：Oracle SQL Loader 详解。

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*LoaderExternal Table
加载推拉
控制控制文件DDL
复杂复杂简单
并行是是
直接路径是是
灵活中高
推荐旧新

15. 常见坑与排错

15.1 ORA-01756

- 引号
- 检查

15.2 ORA-01858

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

15.3 索引失效

- 直接路径
- 重建索引

15.4 锁

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

16. 最佳实践

  1. 直接路径：性能
  2. 并行：吞吐
  3. UNRECOVERABLE：日志
  4. NOLOGGING：归档
  5. DISABLE INDEX：加载后
  6. TRAILING NULLCOLS：NULL
  7. SQL 函数：转换
  8. 多表：复杂
  9. 日志监控：质量
  10. 替代外部表：现代

17. 参考资料

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