做Oracle开发和运维的人对SQLLoader应该都不陌生。Oracle 12c时代它依然是批量数据导入里最常被想起的工具之一。我第一次在一个12c生产环境里接手大批量数据迁移时也一度怀疑这个老工具还能不能撑住实测结论是核心机制没变该用还是用而且在12c的新架构下它反而跟In-Memory、标识列这些新特性有了不少协同配合。SQLLoader本质就是一个命令行批量加载工具你写一个控制文件告诉它数据怎么进来它负责把外部数据文件里的行按规则灌进目标表。它解决的场景是那些几十万、几百万行甚至更大的外部数据文件快速进Oracle同时数据格式可能还不太规整逗号分隔、固定宽度、引号包裹、日期格式怪异都能用一套控制文件规则兜住。适合谁呢运维DBA、后端开发、数据工程师都会碰到。尤其是经常做历史数据迁移、报表数据清洗、合作方数据交换的人SQL*Loader是你的常备基本功。这篇文章我不想把官方文档复述一遍而是把我在12c环境里用这个工具的实操经验拆开讲清楚包括控制文件怎么写、直接路径怎么用、踩过的坑怎么排。看完你至少能独立写一个像样的加载脚本并且知道出了问题先看哪里。1. 12c时代为什么还要回头认识SQL*Loader1.1 它到底解决什么问题先对齐一下SQLLoader的定位。你手头有一份外部数据文件可能是业务方给的CSV可能是旧的订单流水文本也可能是某个老旧系统导出的定长文件。这份数据要导进Oracle的某张表你用Data Pump不合适那是Oracle内部格式的备份与传输解决的是Oracle到Oracle的搬迁。你写INSERT脚本一条条插入几十万条数据光网络往返、SQL解析就能让你等到怀疑人生。这时候SQLLoader就是那个“闷头干活”的角色一条命令把文件扫进去逐行解析、转换、写入整个过程不经过交互式SQL。它在12c里依然是默认安装的这一点值得庆幸。Oracle对老工具做过好几次“退休警告”比如老的imp/exp被数据泵替代但SQLLoader一直保留着说明它在数据搬运场景里不可替代。你可能觉得外部表也能读外部文件确实可以但外部表本质是让Oracle把外部文件当成一张只读表来查每次查询都要重新扫描文件如果你要的是“把文件变成表里实实在在的数据”那SQLLoader更直接文件读一遍行写进表加载完成即结束。1.2 别把它和数据泵、外部表混为一谈我和不少同行聊过发现大家对这几个工具的边界经常搞混这里用一句话区分数据泵expdp/impdpOracle到Oracle备份、迁移、整库或Schema级搬运数据文件是Oracle私有格式。外部表External Table把外部数据文件映射成一张可查询的表适合“查”和“ETL中间层”不落本地表。SQL*Loader外部数据文件到Oracle表一锤子买卖灌进去就算完。实际工作中这三个工具经常搭配用。比如先用SQLLoader把原始文件灌进一张临时表再用SQL做清洗转换最后用数据泵或者普通INSERT进入正式环境。SQLLoader的角色是“入口”它的强项是解析和写入不是业务转换。1.3 12c给SQL*Loader带来了什么说句实在话SQL*Loader的语法从10g到19c基本是延续的12c没有把它重写也没有颠覆性变化。但如果你正好用到了12c的新功能有几个点值得注意表或分区启用了In-Memory列式存储后通过直接路径加载的数据可以绕过Buffer Cache直接为列式存储的构建或后台填充做好准备对实时分析类环境友好。12c引入的标识列Identity Column在控制文件里可以留空或配置生成逻辑直接路径加载不会像老版本那样因为缺少默认值而翻车。目标字段如果是JSON类型控制文件加载的路径比老版本先塞CLOB再转换省了一步。对带有大分区、多分区的表直接路径加载的稳定性明显好于10g、11g时代。这些点不是让你为了新特性而去用而是告诉你如果你的数据库已经升级到12c那么原来那些SQL*Loader脚本不需要大改照样能跑而且在新特性的边缘能白捡一点性能红利。2. 控制文件是灵魂——读懂与手写2.1 一个最小可运行的控制文件长什么样几乎所有SQLLoader任务都从一个控制文件开始。这玩意儿不复杂但它的语法非常“死板”多一个空格、少一个分号都可能直接报SQLLoader-350。先看一个最简版LOAD DATA INFILE users.csv INTO TABLE t_user FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY ( user_id, user_name, sign_time DATE YYYY-MM-DD HH24:MI )对应数据文件可能是这样USER001,某项目成员,2024-01-15 08:30 USER002,普通用户,2024-01-15 09:10然后命令行执行sqlldr your_user/oracleorcl controlusers.ctl logusers.log badusers.bad命令里your_user/oracleorcl是数据库连接串control指定控制文件路径log是运行日志bad是坏记录文件。如果一切顺利日志末尾会显示多少行成功加载、多少行失败。这个流程就是SQL*Loader的最小闭环。控制文件第一行LOAD DATA是固定头别动它。INFILE指定要读取的数据文件。INTO TABLE后面跟目标表名。FIELDS TERMINATED BY ,告诉它字段之间用逗号分隔OPTIONALLY ENCLOSED BY 表示每个字段可以被双引号包起来但不是必须的。这两个配置组合在一起就是处理标准CSV文件的黄金搭档。括号里是字段列表。顺序就是数据文件里列的顺序每行的名字默认对应目标表的列名。日期列必须显式给出格式掩码否则它只会把字符串原样塞给目标列等着数据库报ORA-01843。我见过太多人忽略这一句然后去日志里对着报错发呆。2.2 空值处理NULLIF和DEFAULTIF不能省数据文件里最脏的东西不是乱码而是“看起来什么都没有实际有内容”的字段。比如CSV里这么写USER003,,2024-01-15 10:00第二列是两个连续逗号之间的空字符串如果目标列是VARCHAR2倒还能插进去如果目标列是NUMBER直接报ORA-01722无效数字。更隐蔽的是有的文件里空值变成了一串空格或者被引号包住的空格串。处理办法是在字段后面加NULLIF条件( user_id, user_name NULLIF (user_name BLANKS), sign_time DATE YYYY-MM-DD HH24:MI )NULLIF (user_name BLANKS)意思是如果这个字段的值是空白完全由空格组成或为空就把它当成NULL处理。BLANKS是SQL*Loader里专门表示空白的条件判断它在处理数字列之前的清洗场景下极其好用。还有DEFAULTIF它和NULLIF是兄弟关系。NULLIF是有条件地置NULLDEFAULTIF是有条件地使用目标列的默认值。比如status CHAR(1) DEFAULTIF (status BLANKS)如果status字段为空就用表定义时这个列的默认值来填充。这个特性在表结构里已经配了DEFAULT约束时特别省事不用在控制文件里写死值。2.3 FILLER字段和POSITION定长格式CSV之外的另一个常见场景是定长文件。你在银行、运营商、物流行业经常能拿到这种格式比如每行规定前10位是用户ID11到20位是手机号21到50位是备注。这种文件用分隔符是没法处理的得用POSITION指定起止位置LOAD DATA INFILE members.dat INTO TABLE t_member ( user_id POSITION(01:10), mobile POSITION(11:20), remark POSITION(21:50) )POSITION(01:10)表示截取每一行的第1到第10个字符作为user_id的值。这个功能在处理老系统导出文件时几乎是救命稻草因为定长文件没有逃逸规则也没有引号干扰对齐就是一切。如果源文件里有些列你不需要用FILLER关键字占位( user_id POSITION(01:10), temp_field POSITION(11:20) FILLER, mobile POSITION(21:30) )FILLER字段会被读取但不会写入目标表相当于“读取并跳过”。这个关键字在你有100列文件但目标表只要其中30列时能让控制文件保持清晰。2.4 一个物理行对应多个逻辑记录这算是控制文件的进阶玩法。有的数据文件不是“一行一条记录”而是“几行拼成一条记录”或者“一行里有好几条记录”。前者用CONTINUEIFLOAD DATA INFILE multi_line.dat CONTINUEIF NEXT PRESERVE (1 1) INTO TABLE t_event ( event_id, event_desc )意思是如果读取下一行时满足了某种条件就把这行拼到上一条逻辑记录后面。更简单的场景是用CONCATENATE 2直接把连续的两行合并成一条逻辑记录。不过说实话这种文件在实际业务里不多只要知道SQL*Loader接得住别到时候被吓住就行。3. 一次完整的导入实操从CSV到表3.1 场景设定与表结构准备我拿一个实际做过的项目来举例。当时某支付清算项目要每天从合作方收到一份用户标签文件CSV格式GBK编码行数在20万到80万之间浮动。目标表结构建在Oracle 12c里create table t_user_tag ( user_id varchar2(32), tag_code varchar2(20), tag_value number(10,2), sign_time date, batch_no varchar2(20), remark varchar2(500) );注意我加了一个batch_no字段用来记录这是哪一批导入的数据这个字段在控制文件里用CONSTANT生成源文件里并没有这一列。这类“批次号”“来源标识”是非常实用的设计以后排查数据归属时一眼就能看出来。数据文件user_tags.csv的典型行是USER001,TAG_DIAMOND,128.50,2024-01-15 08:30:00,备注信息可能包含逗号,这里的引号会被正确解析 USER002,TAG_SILVER,30.00,2024-01-15 09:10:00,,第5列和第6列可能为空甚至连续两个逗号之间什么都没有。这种“最后几列空着”的情况如果控制文件不处理会出现字段数不够的报错。3.2 编写并执行控制文件基于这个场景控制文件我这样写LOAD DATA CHARACTERSET ZHS16GBK INFILE user_tags.csv INTO TABLE t_user_tag APPEND FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY TRAILING NULLCOLS ( user_id, tag_code, tag_value NULLIF (tag_value BLANKS), sign_time DATE YYYY-MM-DD HH24:MI:SS, batch_no CONSTANT BATCH_20240115, remark )几个关键点拆开讲CHARACTERSET ZHS16GBK显式声明数据文件的字符集。这一步不能省尤其是你的数据库字符集是AL32UTF8时如果环境变量NLS_LANG没有配好SQL*Loader会按照操作系统默认字符集去读文件中文备注很可能变成乱码。显式声明能避免这个坑。APPEND表示追加模式。如果表里已经有数据不会清空新数据直接追加。对应还有INSERT要求表为空、REPLACE先删除表中所有行再加载、TRUNCATE先TRUNCATE表再加载。全量替换文件的场景我推荐用TRUNCATE因为它比REPLACE的逐行DELETE快得多但要注意TRUNCATE之后如果加载失败老数据也没了使用前要权衡。TRAILING NULLCOLS的作用是当数据行里的字段数少于控制文件里声明的字段数时自动把缺失的字段补成NULL。比如上面例子里的最后两列是空的没有这个语法SQL*Loader会报“记录中的字段数不足”。加上它文件里那些“尾巴上缺字段”的行就能被稳稳接住。batch_no CONSTANT BATCH_20240115是固定值填充。每一条被加载的行batch_no都会写入这个常量后面做数据回溯时直接按批次号查就行。执行命令NLS_LANGAMERICAN_AMERICA.ZHS16GBK sqlldr your_user/oracleorcl \ controluser_tags.ctl loguser_tags.log baduser_tags.bad \ discarduser_tags.dsc这里我把NLS_LANG也在命令行显式指定了双重保险。discard后面是丢弃文件被WHEN条件过滤掉的行会写到这里和坏记录文件是两回事。这个文件我没在控制文件里写WHEN过滤条件但因为加了discard参数一旦以后加了条件能直接看到被丢弃的行。3.3 执行后的验证与增量加载加载完先别急着高兴去日志文件看两个数字Table t_user_tag: 780000 Rows successfully loaded. 12 Rows not loaded due to data errors.“Rows successfully loaded”必须和源文件的有效行数对上。“Rows not loaded”必须能解释清楚它们在bad文件里。我当时的习惯是加载完立刻跑一条SQLselect count(*) from t_user_tag where batch_no BATCH_20240115;用批次号核对一目了然。如果业务上不是全量替换而是每天增量更新常见做法不是直接往正式表里灌而是先加载到一张临时表再用MERGE写进正式表merge into t_user_tag_target t using user_tag_stage s on (t.user_id s.user_id and t.tag_code s.tag_code) when matched then update set t.tag_value s.tag_value, t.sign_time s.sign_time when not matched then insert (user_id, tag_code, tag_value, sign_time, batch_no) values (s.user_id, s.tag_code, s.tag_value, s.sign_time, s.batch_no);SQL*Loader管的是“把文件变成临时表里的数据”正式表的去重、更新、历史归档交给SQL来做。这种分工是我在十几个项目里验证下来最稳的组合。4. 12c下的提速手段与高级玩法4.1 直接路径到底有多快代价又是什么默认的SQL*Loader是传统路径加载相当于程序帮你生成了很多条INSERT语句一条条提交。这种方式走SQL引擎、写Buffer Cache、产生UNDO慢是必然的。数据量大时唯一的正解是直接路径加载命令里加参数sqlldr your_user/oracleorcl controluser_tags.ctl \ logload.log badload.bad directtrue直接路径的底层逻辑是构造好Oracle数据块格式的数据绕过SQL引擎和Buffer Cache直接在表的高水位线以上插入新块。它跳过了大量原本开销巨大的环节所以加载速度能比传统路径快一个数量级。但是代价得说清楚不然你会在生产环境被坑表上如果有活动事务直接路径加载会报错。需要确保加载窗口内没有会话在改这张表。触发器不触发。如果业务依赖表的DML触发器去做审计或者级联操作直接路径会直接绕过。引用完整性约束可能不被检查。唯一索引如果遇到重复值报错的行为和传统路径不同有时会把整个加载中止。直接路径会推进表的高水位线换句话说表占用的物理空间会涨即使逻辑上有些块是空的。所以我的经验是大表有多个普通索引时加载前先DROP索引加载完再重建。听着有点反直觉但全表扫描灌几百万行数据时卸载索引再重建往往比一边灌一边维护索引快得多尤其12c的并行索引重建很快。4.2 真正有用的并行加载配置很多人以为加一个paralleltrue就万事大吉其实不是。paralleltrue只是允许SQL*Loader在直接路径模式下进行某种程度的并行处理真正的大规模并行来自手动分片把数据文件分成多份同时起多个sqlldr进程每个进程加载一部分。实操上我是这么做的把80万行的文件用split命令按行数切成四份。给每份文件单独写一个控制文件语法完全相同只有INFILE文件名不同。写一个Shell脚本后台依次启动四个管道中的sqlldr进程。sqlldr user/pass controlpart1.ctl directtrue paralleltrue sqlldr user/pass controlpart2.ctl directtrue paralleltrue sqlldr user/pass controlpart3.ctl directtrue paralleltrue sqlldr user/pass controlpart4.ctl directtrue paralleltrue wait注意paralleltrue需要目标表上没有活动的修改操作并且几路加载同时往同一张表追加数据靠数据库的并行机制协调。这个模式出来的速度我用单机环境实测比单纯directtrue还能再快两三倍。瓶颈往往在磁盘I/O和归档日志生成速度上。还有一个参数值得记rows。在传统路径下它控制每多少行提交一次默认是64。如果你因为某些原因必须用传统路径把rows调大能明显减少提交次数sqlldr ... controlxxx.ctl rows5000不过记住直接路径下rows失效它是传统路径专用参数。4.3 数据过滤WHEN、表达式和外部表兜底控制文件里可以在INTO TABLE前加WHEN条件做行级过滤。比如我只想加载状态为正常的数据INTO TABLE t_user_tag WHEN (status NORMAL)满足条件的行进入目标表不满足的行写到discard文件。这个机制是用来做“加载前过滤”的比读入后再DELETE一套SQL要高效。字段级转换也很有用控制文件支持用SQL表达式处理字段值( user_id, mobile REGEXP_REPLACE(:mobile, [^0-9], ) )这个写法里字段名后面跟一个双引号包起来的SQL表达式字段值用:字段名引用。比如上面的表达式就去掉了手机号里的非数字字符。日期清洗、字符串截断、大小写转换都可以这么做。如果清洗逻辑实在太复杂超过了几条表达式能表达的范围我的建议是别硬用SQL*Loader的表达式硬扛改用外部表方案。把CSV挂成Oracle外部表然后用SQL的MERGE、多表JOIN、CASE WHEN去做清洗转换最后INSERT进正式表。12c的外部表并行访问已经很成熟这个方案在需要大量业务判断的ETL场景里可读性和维护性远好于揉成一团的控制文件表达式。5. 常见问题速查与排障手记5.1 高频报错与解决对照下面这张表是我在实际运维中整理的高频报错速查尤其是12c环境里遇到的基本涵盖了80%的情况报错或现象常见原因处理方式SQL*Loader-304 记录长度超过最大值数据行因为引号不配对或字段分隔符错乱被读成超长行检查源文件是否有全角逗号、没闭合的引号确保行结束符统一为LF或CRLFORA-01722 无效数字数值字段里混入了空字符串、空格、不可见字符给数值列加NULLIF (字段 BLANKS)清洗源文件检查字段是否被错误的逗号切分ORA-12899 列值过长源字段长度超过目标列定义长度在控制文件里用SUBSTR(:字段, 1, n)截断或加长目标列ORA-26002 直接路径加载失败目标表存在活动事务或正在被修改确保加载窗口内表处于静止状态必要时在维护窗口操作ORA-1400 无法插入NULL非空列没有提供值用CONSTANT固定值、DEFAULTIF调用默认值或先确认数据源完整性SQL*Loader-350 控制文件语法错误关键字拼错、字段列表括号不匹配、文件换行异常打开控制文件逐行检查从网上复制的控制文件要重新确认换行符ORA-01722是我遇到最频繁的。90%的情况不是源文件真的有问题而是CSV里某个空字段在数值列上变成了空白串。SQL*Loader处理空串和NULL是两个完全不同的行为空串塞进NUMBER列就会无效数字这个坑不踩一次不会长记性。5.2 排障三板斧LOG、BAD、DISCARD加载失败后第一件事永远是先看日志文件不是先改SQL。log文件记录了完整的加载过程加载了多少行、失败了多少行、失败的具体错误码都会出现在末尾。bad文件保留了解析失败的原始记录一行对应源文件里一个有问题的数据行。你可以直接打开看是第几行、长什么样。discard文件则不同它记录的是被WHEN条件“合法过滤掉”的行不是错误行。很多新手会把discard和bad混在一起其实区别很清晰bad是“想处理但处理不了”discard是“按规则主动不要”。如果你在控制文件里配了WHEN条件最好显式指定discard文件不然过滤行都丢了你想复核都没机会。排查时我的习惯流程是拉出log文件末尾的加载统计。如果bad文件有内容取第一条记录对照源文件所在行。用编辑器打开该行开启“显示所有字符”功能查有没有尾随空格、制表符、回车换行混用。用SQL*Loader单独加载这一行到一张结构相同的测试表复现错误。确认修复逻辑后再全量加载。这个流程我用了很多年能在五分钟内定位绝大多数数据格式问题。5.3 个人经验和真正的优化建议最后说点我在多个项目里沉淀下来的经验你可以直接抄作业。第一控制文件必须进版本库。它和SQL脚本一样是工程资产不是临时工具。哪天数据文件格式发生调整你还能翻出历史控制文件对比差异。第二字符集永远显式指定。我不止一次因为图省事没写CHARACTERSET结果加载完发现中文全部是乱码。这种数据错误比加载失败还危险因为日志里看不出任何问题直到业务方在用数据时才发现。第三大批量导入前先做一个小文件试跑。不用多几百行就够。重点看log文件里的错误行和bad文件确认控制文件和源文件格式完全对应后再放开跑全量。这一步节省的时间远超试跑的几分钟。第四直接路径加载完关注一下表的高水位线。如果一次性灌入百万行级别数据这个表后续的索引重建、统计信息收集都要跟上。别忘了执行exec dbms_stats.gather_table_stats(user, T_USER_TAG, cascade true);统计信息不更新12c的优化器很容易在后续查询里选错执行计划。第五加载是IO密集型任务别挑业务高峰期。普通传统路径会占用大量Buffer Cache直接路径也会带来大量归档日志。给DBA留点面子把这些活安排到维护窗口做大家都会感谢你。SQL*Loader这个工具看着不起眼关键时刻能顶半边天。掌握控制文件的几个关键语法理解直接路径的取舍学会看日志排错你基本就能从容应对日常90%以上的批量导入需求了。
