最近在给一个业务系统做数据迁移，生产库是12c，第三方平台导出了一大批文本文件，字段分隔符还不统一，有的是逗号，有的是竖线，日期格式也是五花八门。我第一反应是写存储过程逐行解析，但回头一想，这种活本来就有专门的工具干，何必自己造轮子——Oracle自带的SQL*Loader就是干这个的。
在12c这个版本里，SQL*Loader虽然已经是个“老家伙”了，但它依然是日常批量导入最顺手、最稳定的方案，尤其适合处理各种格式不那么规整的文本文件。这篇文章我不打算照抄官方文档，而是结合我实际用下来的经验，把这个工具从原理到实操完整过一遍，顺便说说12c里有哪些地方和旧版本不一样，踩过哪些坑。
1. SQL*Loader到底是什么，和expdp/外部表有什么区别
很多人一提到导入数据就只想到impdp，那是把它当成了EXPDP的配套工具。实际上SQL*Loader的定位完全不同：它专门负责把操作系统上的外部数据文件（通常是文本格式）加载到Oracle数据库表中，而不是在Oracle实例之间搬数据。
打个比方，如果数据库是一栋房子，EXPDP/IMPDP干的活是把整栋房子的家具原封不动搬到另一栋房子，而SQL*Loader是把一堆散装的零件按清单一件一件组装进这栋房子。前者讲究结构完整性，后者讲究格式可解释性。
那它和外部表（External Table）又有什么区别？外部表也能读文本文件，但它的逻辑是“文件当表查”，你查询的时候它去读文件，文件本身不进入数据库。而SQL*Loader是把文件里的数据真正写入数据库，成为表里的物理记录。外部表在数据量小、临时分析时很方便，但大批量持久化导入，SQL*Loader依然是更优的选择，尤其是它支持直接路径加载（Direct Path Load），可以绕过常规SQL处理层，速度优势非常明显。
在12c版本里，SQL*Loader的架构没有革命性变化，但有几个细节值得注意：
- 支持通过**目录对象（Directory Object）**来定位数据文件，权限管理更规范，不再建议直接写操作系统绝对路径。
- 对标识列（Identity Column）、12c新增的数据类型（如基于JSON的存储）有了更好的兼容性。
- 控制文件语法仍然兼容旧版本，11g时代的脚本基本可以直接拿过来用。
2. 控制文件与数据文件：这两个家伙才是核心
用SQL*Loader导入数据，最少需要三步：准备数据文件、编写控制文件、执行sqlldr命令。其中最有含金量的是控制文件，它决定了数据文件里的每一列如何被解释。
2.1 控制文件的基本结构
一个最简控制文件长这样：
load data infile 'emp_data.txt' into table emp fields terminated by ',' optionally enclosed by '"' ( empno, ename, job, hiredate date 'yyyy-mm-dd', sal )我逐行解释一下：
load data：固定开头字句，告诉SQL*Loader“下面定义的是加载任务”。infile 'emp_data.txt'：指定数据文件的路径。12c下这里可以使用目录对象，比直接写绝对路径更安全。into table emp：目标表。fields terminated by ',' optionally enclosed by '"'：字段以逗号分隔，个别字段的值可能用双引号包起来，optionally表示引号可选。- 括号里的部分是字段映射：表字段名 + 可选的类型转换。日期字段必须显式指定格式，否则容易出现ORA-01858之类的转换错误。
控制文件里还有一个容易被忽略的选项：append、insert、replace、truncate。这决定了导入数据前对目标表的处理方式，我见过不少人在这里翻车：
insert：要求目标表必须为空，否则直接报错。append：向表中追加数据，最常用。replace：先DELETE掉全表数据再插入。truncate：用TRUNCATE清空表再插入，速度最快，但不能回滚。
我在生产环境导入大批量数据时，如果不是清空重导的需求，一般都选append，避免误删数据。
2.2 数据文件的格式套路
SQL*Loader对数据文件的宽容度其实很高，但有一个前提——控制文件里的定义必须匹配实际格式。我总结了几种最常见的格式套路：
- 定长格式（Positional）：每一行各字段长度固定，用
position(1:10)这样的写法映射。这种格式适合银行、电信等系统导出的流水文件，处理速度极快。 - 分隔符格式（Delimited）：字段之间用逗号、竖线、Tab等分隔，
terminated by ','或terminated by X'09'。 - 二者混合：部分字段定长，部分字段分隔——这种情况真实系统里经常出现，SQL*Loader也能处理，只要控制文件定义清楚就行。
日期字段是重灾区。文本文件里日期显示为2022-08-31，但控制文件里没写date 'yyyy-mm-dd'，导入时就报ORA-01858。更麻烦的是毫秒、时区这类新格式，我建议先在控制文件里全部按to_date或date '...'规范好，宁可在控制文件里多写几行转换，也不要在数据库端事后修数据。
3. 12c下SQL*Loader的命令行参数，哪些值得认真看
sqlldr命令本身有几十个参数，但实际工作中高频使用的就那么几个。我整理了一张表，方便对照：
| 参数 | 作用 | 我的习惯用法 |
|---|---|---|
| userid | 用户名/密码，格式用户名/密码@服务名 | 生产环境建议用外部认证或wallet，别明文 |
| control | 控制文件路径 | 必填 |
| log | 日志文件路径 | 指定独立路径，方便排查 |
| bad | 坏文件路径 | 记录完全无法解析的坏行 |
| discard | 丢弃文件路径 | 记录被过滤条件丢弃的行 |
| rows | 多少条提交一次（常规路径） | 千行或万行，视事务大小而定 |
| direct | 是否使用直接路径加载：TRUE/FALSE | 大批量时用TRUE |
| parallel | 是否并行加载 | 多文件并行时用TRUE |
| skip_index_maintenance | 直接路径下是否维护索引 | 大表可临时TRUE，导完再重建 |
我发现很多人忽略skip_index_maintenance这个参数。直接路径加载时默认会同步维护索引，但如果表上有多个索引，维护成本会很高。12c里你可以设置skip_index_maintenance=TRUE先不维护索引，导入完成后再用ALTER INDEX ... REBUILD统一重建，整体时间往往能省不少。需要注意的是，在这个过程中查询会走不到这些索引，所以最好选业务低峰期操作。
另外一个在12c里用起来很顺手的是TABLE参数配合外部表方式。SQL*Loader可以把控制文件转成外部表访问定义，相当于把文本文件变成数据库里的一个只读视图，方便你先查一下数据质量再决定要不要真正导入。这个思路特别适合“数据看着不对劲，想先看看再导”的场景。
4. 实战案例：从零导入一批人员档案数据
光讲参数太干了，我直接用一个实际案例把整个流程串一遍。假设要导入一份人员档案文件person_20240828.txt，竖线分隔，含姓名、身份证号、入职日期、工资，部分日期可能为空。
4.1 建表和准备控制文件
建表语句就不贴了，控制文件我写成了这样：
load data infile 'person_20240828.txt' append into table person fields terminated by '|' ( name, id_number, hire_date char(10) date 'yyyy-mm-dd', salary "to_number(:salary, '99999.99')" )这里有两个细节：
第一，hire_date char(10) date 'yyyy-mm-dd'先定义成CHAR长度为10，再转DATE，避免因为文件中某些值前后有空格导致转换失败。
第二，工资字段用了SQL字符串"to_number(:salary, '99999.99')"。在控制文件里，字段名前面带冒号表示引用该字段的原始值，你可以对它做任意SQL表达式转换。这意味着控制文件不只是“取字段”，它还是一个微型ETL工具，可以在导入过程中完成格式化、清洗、甚至关联其他表。
4.2 执行导入命令
执行命令通常长这样：
sqlldr userid=scott/tiger@orcl12c control=load_person.ctl log=load_person.log bad=load_person.bad discard=load_person.dsc执行完第一件事不是看结果行数，而是看日志文件的最后几行：
Table PERSON: 76 Rows successfully loaded. 3 Rows not loaded due to data errors. 1 Row not loaded because all WHEN clauses were failed. 0 Rows not loaded because all fields were null.这里逻辑还不算复杂，但真实生产中动辄几十万行，出现几百行坏数据很常见。重点看bad文件里被拒的行长什么样，而不是急着调控制文件重跑。
4.3 修数据重导的正确姿势
坏文件里的行通常是格式问题，比如身份证号里混了特殊字符、日期是2024/08/28但控制文件要求yyyy-mm-dd。这时候我的做法是：
- 先看坏文件样本，归纳错误类型。
- 在控制文件里增加清洗逻辑，比如使用
regexp_replace、trim、to_date兼容多格式。 - 只重新导入坏文件中的行，而不是把整个大文件再导一遍。
有个小技巧：SQL*Loader支持infile指定多个文件，并且可以配合WHEN条件只加载符合条件的数据。比如把坏文件改名后，在控制文件里加一段WHEN (1) = '5'之类的条件，只处理坏数据行——当然实际场景可以根据行特征判断。
5. 日志、坏文件、丢弃文件：0到1排查问题的完整链路
很多初学者碰到导入报错就慌，其实SQL*Loader的三件套——日志文件、坏文件、丢弃文件，已经把排查路径标得很清楚了。
5.1 日志文件怎么读
日志文件的内容分几块：环境信息、控制文件定义回显、每一张表的加载统计、最后的错误汇总。我最常看的是这几行：
Record 308: Rejected - error on table PERSON, column HIRE_DATE. ORA-01858: a non-numeric character was found where a digit was expected它直接告诉你是第几行、哪个字段、什么错误。看明白这条信息，绝大多数问题都能定位。日志末尾还会给出每个字段的“最大长度”“平均长度”统计，方便你确认数据质量，不用去数文件里到底谁超长了。
5.2 坏文件和丢弃文件的区别
这两个概念很容易混淆：
- 坏文件（bad file）：数据本身格式有问题，无法被解析，或违反了表的约束，比如NOT NULL但字段为空、主键重复。
- 丢弃文件（discard file）：数据本身没问题，但被控制文件中的
WHEN条件或NULLIF等规则过滤掉了，属于“合法但不需要”。
比如你只想导部门编号为01的数据，控制文件里写WHEN dept_id = '01'，那其他部门的数据就会进丢弃文件。这时候看丢弃文件不是错误，而是确认过滤条件是否符合预期。
5.3 一次典型的排错过程
有一次导入大批量订单数据，日志显示几千行报ORA-01400（不能将NULL插入非空字段），但数据文件里明明这些字段是有值的。我打开坏文件一看，发现这些行的字段错位了——因为订单描述字段里包含了竖线分隔符，导致后续字段整体后移。
解决办法是在控制文件里给描述字段加上optionally enclosed by '"'，让解析器把引号内的竖线视为数据内容而不是分隔符。这种问题从日志里很难直接看出来，一定要结合实际坏文件样本对照。
6. 性能调优与12c相关新特性的实际运用
导数据最怕等，几十万行跑一个钟头谁也受不了。SQL*Loader的性能优化有几个关键开关，我按优先级排一下。
6.1 直接路径加载（Direct Path）到底省在哪
常规路径加载相当于一条条INSERT，走完整的SQL处理层，要写重做日志、要维护约束和索引；直接路径加载则是把数据块按Oracle内部格式组装好，直接写入表空间，绕过了大量开销。开启方式很简单：
sqlldr userid=... control=... direct=true rows=10000需要注意的是，直接路径加载会暂时禁用表上的触发器，且对表的约束状态有要求。生产环境里如果有触发器依赖于导入动作，用之前要仔细核对逻辑，否则容易漏处理。
6.2 并行加载的姿势
SQL*Loader的并行是把一个大文件拆成多个小文件，同时起多个会话并行加载。控制文件里写：
load data infile 'part1.txt' infile 'part2.txt' ...然后命令行里加parallel=true。实际执行时可以开多个sqlldr进程，每个进程处理一部分。这里的瓶颈往往是磁盘I/O和数据文件格式转换，所以并行度并不是越高越好，我一般先试4个并行进程，看资源消耗监控再逐步调整。
6.3 12c下值得关注的特性点
12c引入了一些与SQL*Loader交互相关的特性，不全是它的专属功能但实际导入时能用到：
- 标识列（Identity Column）：如果目标表的ID字段是标识列，控制文件里不映射它即可，数据库会自动生成。
- JSON支持：文本文件中包含JSON字符串的列，可以先作为CLOB/VARCHAR2导入，再用12c的JSON函数处理，比在外部写程序解析高效。
- 部分分区导入：配合分区表，SQL*Loader可以通过
WHEN或表映射把不同数据导到不同分区，减少一个大表全量扫描的压力。 - 外部表改造：就像前面说的，控制文件可以生成外部表定义，先用SQL查一下文件内容，再做导入决策。这在12c里已经非常成熟，值得多用。
6.4 一个调优实例
我帮某公司导过一张日流水表，原始文本文件约2GB，3000万行，普通网络盘存储，服务器配置不算高。第一次用默认参数跑，预计要将近80分钟。调整思路是：
- 改成
direct=true - 把文件按月份拆成12个文件
- 每4个sqlldr进程并行跑
- 表上的索引先DROP，导入完成后再重建
- 使用
rows=50000批量参数
最后总耗时大概12分钟，差不多有六倍多的提升。对于BI系统这类导入，这类优化基本是必须的。
7. 用SQL*Loader时最容易踩的几个坑
最后集中说一下我这些年用SQL*Loader踩过的坑，有代表性的列几个：
坑一：忽略字符集。源文件如果是GBK编码，而数据库字符集是AL32UTF8，则中文字段会导入成乱码。解决办法是在控制文件里加CHARACTERSET ZHS16GBK或者在命令里指定charset参数。这个问题在12c依然高发，因为日常碰到的文件编码确实太杂。
坑二：日期格式默认依赖会话NLS设置。控制文件里如果不显式写明date格式，解析时用的是会话的NLS_DATE_FORMAT，非常不可控。我的习惯是控制文件里所有日期字段一律写date '...'，哪怕看起来“多余”。
坑三：控制文件换行和注释。SQL*Loader控制文件的语法对换行有要求，尤其是INTO TABLE和字段列表之间，不能随意注释。字段列表末尾多一个逗号，报错时的提示又不够明显。我每次写完控制文件都会用sqlldr ... control=...先做一次小数据量验证，避免直接跑全量。
坑四：数据文件末尾多余的空行。文件最后如果有一个空行，导入时它会被当成一条“全字段为空”的记录，报错或丢弃都是小事，有时会莫名多产生一条空数据。控制文件里可以用TRAILING NULLCOLS配合，或者干脆导入前先清洗文件。
坑五：字段顺序千万别靠“看起来一致”。控制文件字段映射是和目标表的列名对应的，不是按数据文件的物理位置硬排的——当然定长格式除外。如果数据文件和表结构字段顺序不一致，一定要在控制文件里逐个写明映射关系，不要偷懒只写字段名列表去“猜”。
8. 最后再分享一个提速的小技巧
如果你想在正式导入前快速验证控制文件是否正确，可以先用ROWS=1跑一次，并且把数据文件指向一个只含几行测试数据的小文件，看日志里的加载统计。这一步能省去大量调试时间，因为你不需要等全部数据都跑完再发现在哪一行挂掉。
另外，如果数据文件太大，可以先分割成几个小文件分别验证，确认无误后再合并跑全量。我用过的分割工具很多，Unix下split -l 10000就很顺手，Windows下可以用文本处理工具或者自己写个小脚本按行数拆分。批量导入这种事，最忌讳的就是“一把梭”，拆开验证再合，反而更快。
SQL*Loader在12c里虽然不是什么新东西，但论批量文本导入的效率和可控性，老工具的优势还是稳的。控制文件这东西初看觉得语法冷门，真正用熟之后你反而会喜欢上这种“一切皆文本”的透明感——数据怎么映射、怎么清洗、怎么过滤，全写在一个文件里，可审查、可版本管理、可复现。这比很多图形化导入工具的黑盒逻辑要靠谱得多。
