1. 从Excel到Oracle为什么SQLLDR依然是数据迁移的“定海神针”最近在帮一个朋友处理一个数据迁移的活儿他们业务部门给过来一个将近50万行的CSV文件需要快速导入到Oracle 19c的生产环境里。朋友第一反应是“用Python写个脚本或者找个ETL工具不就行了” 我笑了笑让他先别急然后打开了命令行。半小时后数据安安稳稳地躺在Oracle的表里整个过程只用了两个文件一个CSV一个不到十行的控制文件。用的工具就是今天要聊的主角——Oracle SQLLDRSQL*Loader。在这个Python、Pandas、各种图形化数据集成工具满天飞的时代为什么还要提这个看起来有点“古老”的命令行工具原因很简单在特定场景下它依然是最高效、最直接、最可靠的“数据搬运工”。尤其是当你面对的是Oracle数据库本身SQLLDR是Oracle亲生的“数据高速通道”它绕过了常规的SQL处理层采用直接路径加载Direct Path Load其速度是任何通过JDBC/ODBC一条条INSERT的程序无法比拟的。对于动辄几十万、上百万甚至上亿条记录的数据文件SQLLDR往往是DBA和开发人员首选的“重型武器”。如果你经常需要从业务系统导出数据、对接第三方数据文件或者做定期批量数据初始化那么掌握SQLLDR就等于掌握了一把打开Oracle数据批量导入大门的钥匙。它不挑前端一个文本编辑器加命令行就能搞定尤其适合在服务器环境进行自动化作业。接下来我就以一个实际的CSV文件导入为例带你走一遍完整的流程并分享那些只有踩过坑才知道的细节。2. 战前准备理解SQLLDR的核心与准备工作在敲下任何命令之前理解SQLLDR的工作原理和准备好“弹药”至关重要。盲目操作很可能导致数据错乱或者导入失败。2.1 SQLLDR的两种模式常规路径与直接路径SQLLDR主要有两种加载模式理解它们的区别决定了你导入的速度和资源消耗。常规路径加载 (Conventional Path Load)这是默认模式。SQLLDR扮演一个客户端角色它读取数据文件生成标准的INSERT语句通过Oracle的SQL引擎来插入数据。这个过程会利用数据库的缓冲区触发触发器维护索引并且完全遵守事务逻辑。它的优点是通用、安全适合数据量不大、表上有复杂触发器或引用完整性约束的场景。缺点就是慢因为要走完整的SQL处理流程。直接路径加载 (Direct Path Load)这是SQLLDR的“性能模式”。它绕过SQL引擎和数据库缓冲区直接格式化数据块并将其写入数据库数据文件。这意味着速度极快通常是常规路径的几倍甚至几十倍。不触发触发器表上的INSERT触发器不会被执行。索引处理在加载期间索引会被置于“直接加载”状态DIRECT LOAD加载完成后才统一更新索引或者需要重建。部分约束暂停可以指定SKIP_UNUSABLE_INDEXESYCONSTRAINTSDISABLED等。需要更多权限通常需要对表有INSERT和SELECT权限并且如果使用直接路径可能还需要一些特殊权限如ALTER TABLE用于禁用约束。对于纯粹的、大批量的CSV数据初始化直接路径加载通常是我们的首选。在控制文件中使用OPTIONS (DIRECTTRUE)来启用它。2.2 你的“弹药库”三个核心文件一次成功的SQLLDR导入离不开三个文件协同工作数据文件 (Data File)就是你的CSV文件。确保它的格式是规整的比如EMPLOYEE_ID,FIRST_NAME,LAST_NAME,HIRE_DATE,SALARY 100,Steven,King,2003-06-17,24000 101,Neena,Kochhar,2005-09-21,17000 102,Lex,De Haan,2001-01-13,17000注意字段分隔符这里是逗号、文本限定符如果有如双引号”、行终止符Windows是\r\nLinux/Unix是\n。文件编码最好与数据库字符集如AL32UTF8一致避免乱码。控制文件 (Control File, .ctl)这是SQLLDR的“大脑”和“指挥手册”是最关键的文件。它告诉SQLLDR数据文件在哪里、什么格式。数据要导入到哪张数据库表、哪个用户下。数据文件的每一列对应目标表的哪一列。采用什么加载模式遇到错误如何处理。 它是一个纯文本文件后续我们会详细编写。日志文件 (Log File, .log)、坏文件 (Bad File, .bad)、废弃文件 (Discard File, .dsc)这些是SQLLDR运行后生成的输出文件。日志文件 (.log)必须仔细阅读它记录了加载的完整过程读取了多少行成功加载了多少多少行因为格式错误进入坏文件多少行因不满足WHEN条件被废弃以及任何错误信息。坏文件 (.bad)存放所有因数据格式错误如日期格式不对、数字字段包含字符而无法被SQLLDR解析的记录。你可以根据这个文件修正数据后重新导入。废弃文件 (.dsc)存放所有不满足控制文件中WHEN子句条件的记录。在开始前请先在数据库创建好目标表。假设我们要导入员工数据表结构如下CREATE TABLE EMPLOYEES_STG ( EMPLOYEE_ID NUMBER(6), FIRST_NAME VARCHAR2(20), LAST_NAME VARCHAR2(25), HIRE_DATE DATE, SALARY NUMBER(8,2) );3. 编写控制文件从入门到精通控制文件的语法看似复杂但结构清晰。我们从一个最基础的例子开始逐步增加功能。3.1 基础控制文件应对标准CSV针对上面提到的CSV文件一个最基本的控制文件load_emp.ctl如下OPTIONS (DIRECTTRUE, ERRORS50, ROWS50000) LOAD DATA INFILE ‘/path/to/your/employees.csv’ APPEND INTO TABLE EMPLOYEES_STG FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘“’ TRAILING NULLCOLS ( EMPLOYEE_ID, FIRST_NAME, LAST_NAME, HIRE_DATE DATE “YYYY-MM-DD”, SALARY )逐行解析OPTIONS (DIRECTTRUE, ERRORS50, ROWS50000)设置加载选项。DIRECTTRUE启用直接路径加载追求速度。ERRORS50允许的最大错误数即进入坏文件的记录数超过此数加载终止。设为0则不允许任何错误。ROWS50000在直接路径加载中表示每次提交的行数绑定数组大小。较大的值可以减少提交次数提升性能但会占用更多内存。需要根据服务器资源调整。LOAD DATA固定关键字声明开始加载数据。INFILE ‘…’指定数据文件路径。可以是绝对路径也可以是相对路径。也可以用INFILE *表示数据就在控制文件末尾不常用。APPEND指定数据加载方式。这是最常用的选项表示向表中追加数据。其他选项还有INSERT插入到空表中如果表有数据则报错。REPLACE先删除表中所有现有数据再插入新数据。TRUNCATE先使用TRUNCATE语句清空表再插入数据。INTO TABLE EMPLOYEES_STG指定目标表。注意如果表不在当前连接用户的默认模式Schema下需要使用SCHEMA.TABLE_NAME格式如HR.EMPLOYEES_STG。FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘“’定义字段分隔符和文本限定符。这行告诉SQLLDR字段用逗号分隔并且字段值可能被双引号包围OPTIONALLY表示可有可无。这是处理CSV的标准配置。TRAILING NULLCOLS一个非常重要的选项。它告诉SQLLDR如果数据文件的某行末尾的列是空的即缺少值应该将这些列视为NULL而不是报错。没有这个选项遇到行尾缺失列的数据时会加载失败。( … )列定义列表。这里定义了数据文件列与表列的映射关系。大多数列名直接对应即可如EMPLOYEE_ID。对于HIRE_DATE列因为数据文件中的日期格式是”YYYY-MM-DD”而数据库默认可能不是所以需要用DATE “YYYY-MM-DD”来显式指定日期格式模型SQLLDR会据此进行转换。这是处理日期数据最常见的坑。3.2 进阶控制处理复杂情况与数据清洗实际数据往往没那么规整。控制文件提供了强大的数据预处理能力。情况一数据文件列顺序与表列顺序不一致假设CSV文件列顺序是LAST_NAME,FIRST_NAME,EMPLOYEE_ID,…但表结构是EMPLOYEE_ID, FIRST_NAME, LAST_NAME,…。只需在列定义中按数据文件顺序列出并映射到正确的表列即可( LAST_NAME “:LAST_NAME”, — 将数据文件第一列读入变量:LAST_NAME FIRST_NAME “:FIRST_NAME”, EMPLOYEE_ID “:EMPLOYEE_ID”, HIRE_DATE DATE “YYYY-MM-DD” “:HIRE_DATE”, SALARY “:SALARY” )实际上简单的重命名映射可以省略变量声明直接写LAST_NAME, FIRST_NAME, EMPLOYEE_ID, …SQLLDR会按位置匹配。但显式使用变量名更清晰。情况二数据清洗与简单转换可以在列定义中使用SQL函数或表达式。例如我们希望导入时自动将FIRST_NAME和LAST_NAME转换为大写并为SALARY增加10%( EMPLOYEE_ID, FIRST_NAME “UPPER(:FIRST_NAME)”, — 导入时转换为大写 LAST_NAME “UPPER(:LAST_NAME)”, HIRE_DATE DATE “YYYY-MM-DD”, SALARY “:SALARY * 1.10” — 导入时计算涨薪10% )情况三条件加载与数据过滤使用WHEN子句可以只加载符合条件的行。例如只导入薪水大于10000的员工INFILE ‘/path/to/your/employees.csv’ APPEND INTO TABLE EMPLOYEES_STG WHEN SALARY 10000 — 条件写在INTO TABLE之后列定义之前 FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘“’ TRAILING NULLCOLS ( … )不满足WHEN条件的行会被写入废弃文件 (.dsc)。情况四处理换行符在字段内的情况有时CSV的某个字段如地址、备注内部包含换行符。标准的TERMINATED BY ‘\n’会误判。这时需要使用非默认的记录终止符并在字段定义中指定CHAR类型和最大长度但更可靠的做法是确保数据文件使用统一的文本限定符如双引号并将换行符包含在限定符内。SQLLDR在解析时能正确识别。4. 执行导入与深度排错从命令到日志分析准备好控制文件和CSV数据文件后就可以在操作系统的命令行中执行SQLLDR了。4.1 执行SQLLDR命令基本命令格式如下sqlldr useridusername/passworddatabase_service_name controlload_emp.ctl logload_emp.log badload_emp.bad discardload_emp.dsc参数详解userid数据库连接字符串。格式为用户名/密码连接描述符。例如hr/hrorclpdb。出于安全考虑不建议在命令行直接暴露密码。可以只写useridhr执行时会提示输入密码。或者在控制文件中第一行用USERID hr指定。control控制文件路径。log,bad,discard指定生成的日志文件、坏文件、废弃文件的路径和名称。如果不指定会使用控制文件的主文件名加上相应后缀。一个更安全的调用方式是使用Oracle的“外部密码存储”或直接在服务器上使用已配置了ORACLE_SID的环境但那是更进阶的话题。对于测试和学习上述命令足够。执行后如果一切顺利你会看到类似下面的输出SQL*Loader: Release 19.0.0.0.0 – Production on Fri May 17 10:00:00 2024 Copyright (c) 1982, 2024, Oracle and/or its affiliates. All rights reserved. Commit point reached – logical record count 50000 Commit point reached – logical record count 100000 …4.2 解读日志文件你的“体检报告”执行完毕第一件事不是去查数据库而是打开日志文件。日志文件包含了本次加载的完整“体检报告”。我们来看一个成功日志的关键部分Control File: load_emp.ctl Data File: /path/to/your/employees.csv Bad File: load_emp.bad Discard File: load_emp.dsc (Allowable Errors: 50) (Direct Load) — 使用了直接路径加载 Table EMPLOYEES_STG, loaded from every logical record. Insert option in effect for this table: APPEND Column Name Position Len Term Encl Datatype —————————— ———- —- —- —- ————————- EMPLOYEE_ID FIRST * , O(“) CHARACTER FIRST_NAME NEXT * , O(“) CHARACTER LAST_NAME NEXT * , O(“) CHARACTER HIRE_DATE NEXT * , O(“) DATE YYYY-MM-DD SALARY NEXT * , O(“) CHARACTER Table EMPLOYEES_STG: 500000 Rows successfully loaded. — 成功加载的行数 0 Rows not loaded due to data errors. — 因数据错误未加载的行数 0 Rows not loaded because all WHEN clauses were failed. — 因不满足WHEN条件未加载的行数 0 Rows not loaded because all fields were null. — 因所有字段为空未加载的行数 Space allocated for bind array: 495360 bytes(50000 rows) — 绑定数组大小 Space allocated for memory besides bind array: 0 bytes Total logical records skipped: 0 Total logical records read: 500000 Total logical records rejected: 0 Total logical records discarded: 0 Run began on Fri May 17 10:00:00 2024 Run ended on Fri May 17 10:00:15 2024 Elapsed time was: 00:00:15.25 — 总耗时 CPU time was: 00:00:05.12这份报告清晰地告诉我们50万行数据15秒完成0错误0废弃。性能非常可观。4.3 常见错误排查与解决但事情往往不会一帆风顺。下面是一些典型的错误及其排查思路错误1ORA-01722: invalid number日志表现在日志文件的错误详情部分会看到此错误对应的行会进入.bad文件。原因试图将一个非数字字符串加载到NUMBER类型的列中。比如SALARY列里混入了”N/A”或”10,000″包含逗号。解决检查.bad文件定位问题数据。在控制文件中可以为该列使用NULLIF或DEFAULTIF。例如如果遇到”N/A”则设为NULLSALARY “NULLIF(SALARY’N/A’)“。但注意NULLIF里的比较值需要与数据文件中的原始字符串完全匹配。更根本的方法是清洗源数据文件。错误2ORA-01861: literal does not match format string原因日期字符串与指定的格式模型不匹配。比如格式指定为”YYYY-MM-DD”但数据中是”17/05/2024″或”May 17, 2024″。解决检查数据文件中的日期实际格式。修改控制文件中的日期格式模型例如改为HIRE_DATE DATE “DD/MM/YYYY”。如果一列中的日期格式不统一这是最棘手的情况。可能需要预处理数据文件或者使用更灵活的TO_DATE函数并处理异常例如HIRE_DATE “TO_DATE(:HIRE_DATE, ‘YYYY-MM-DD’, ‘NLS_DATE_LANGUAGEAMERICAN’)”但这要求格式相对固定。错误3数据被截断如ORA-12899原因数据文件中的字符串长度超过了目标表列的定义长度如VARCHAR2(25)。解决修改表结构增加列宽ALTER TABLE … MODIFY …。在控制文件中使用TRIM或SUBSTR函数截断数据FIRST_NAME “SUBSTR(:FIRST_NAME, 1, 20)“。但这会导致数据丢失。预处理源数据确保长度合规。错误4加载速度异常缓慢可能原因使用了常规路径加载未指定DIRECTTRUE。目标表上有大量活跃的索引。直接路径加载时索引维护会消耗大量时间。ROWS参数设置过小导致频繁提交。磁盘I/O性能瓶颈。优化建议务必使用DIRECTTRUE。对于超大数据量导入考虑在加载前删除或置为UNUSABLE非唯一索引加载完成后重建。唯一索引和主键约束在直接路径加载时通常需要保持但会影响速度。适当增大ROWS参数如从50000到100000但需观察PGA内存使用。将数据文件、控制文件、数据库数据文件放在不同的物理磁盘上减少I/O竞争。使用PARALLEL选项进行并行加载需要更多设置和权限。5. 性能调优与实战技巧让导入飞起来当你掌握了基础操作后这些进阶技巧能帮助你在处理海量数据时游刃有余。5.1 并行直接路径加载对于超大型文件数十GB以上可以使用并行加载将单个数据文件分割成多个部分由多个SQLLDR进程同时加载。分割数据文件使用split命令Linux或其他工具将大的CSV文件分成多个小文件例如data_part1.csv,data_part2.csv。准备多个控制文件每个控制文件指向不同的数据分片但加载到同一个表。关键是要在控制文件中加入UNRECOVERABLE和PARALLELTRUE选项。OPTIONS (DIRECTTRUE, PARALLELTRUE) UNRECOVERABLE LOAD DATA INFILE ‘/path/to/data_part1.csv’ APPEND INTO TABLE EMPLOYEES_STG …UNRECOVERABLE意味着加载的数据不会生成重做日志Redo Log这能极大提升速度但代价是如果加载过程中发生故障这些数据无法通过重做日志恢复必须重新加载。仅在对新表或可以完全重建的数据使用。同时运行多个SQLLDR进程在多个终端或通过脚本同时启动多个sqlldr命令每个使用不同的控制文件。注意并行加载对系统资源CPU、I/O、内存消耗很大并且需要仔细规划避免进程间冲突。通常用于数据仓库的初始装载。5.2 处理大对象LOB和特殊数据类型如果需要导入CLOB或BLOB数据控制文件的写法会有所不同。数据通常需要存储在单独的文件中如每个LOB对应一个.txt或.jpg文件然后在CSV中存储这些文件的路径。假设表有一个RESUME列CLOBCSV中存储的是简历文本文件的路径LOAD DATA INFILE ‘employees_with_resume.csv’ APPEND INTO TABLE EMPLOYEES_STG FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘“’ TRAILING NULLCOLS ( EMPLOYEE_ID, FIRST_NAME, LAST_NAME, HIRE_DATE DATE “YYYY-MM-DD”, SALARY, RESUME LOBFILE(RESUME_FILE) TERMINATED BY EOF — RESUME_FILE是CSV中的文件名列 )这里CSV需要多一列RESUME_FILE其值是类似/path/to/resumes/100.txt的字符串。SQLLDR会读取该文件内容填充到RESUMECLOB列中。5.3 自动化与集成将SQLLDR嵌入脚本SQLLDR非常适合与Shell脚本Linux或批处理脚本Windows集成实现自动化定时任务。一个简单的Linux Shell脚本示例load_data.sh#!/bin/bash # 设置环境变量指向Oracle客户端 export ORACLE_HOME/u01/app/oracle/product/19c/dbhome_1 export PATH$ORACLE_HOME/bin:$PATH export LD_LIBRARY_PATH$ORACLE_HOME/lib:$LD_LIBRARY_PATH # 定义变量 CTL_FILE“/home/oracle/scripts/load_emp.ctl“ LOG_FILE“/home/oracle/scripts/log/load_emp_$(date %Y%m%d_%H%M%S).log“ BAD_FILE“/home/oracle/scripts/bad/load_emp_$(date %Y%m%d_%H%M%S).bad“ USERID“hrorclpdb“ echo “Starting SQL*Loader at $(date)“ # 执行SQLLDR密码通过文件或交互式输入此处示例为交互式 sqlldr control$CTL_FILE log$LOG_FILE bad$BAD_FILE userid$USERID # 检查日志中是否有错误 ERROR_COUNT$(grep -c “Rows not loaded due to data errors“ $LOG_FILE | awk ‘{print $1}‘) if [ $ERROR_COUNT -gt 0 ]; then echo “WARNING: Load completed with $ERROR_COUNT data errors. Please check $LOG_FILE and $BAD_FILE.“ # 可以在这里添加发送报警邮件的逻辑 else echo “SUCCESS: Data loaded successfully at $(date).“ fi然后通过crontab设置定时任务这个脚本就可以在无人值守的情况下运行了。5.4 一个真实的踩坑案例字符集与乱码我曾经遇到一个情况从Windows系统生成的UTF-8 CSV文件导入到数据库后中文字符变成了问号“”。日志文件没有报错数据行数也对但内容错了。排查过程首先检查数据库字符集SELECT * FROM nls_database_parameters WHERE parameter LIKE ‘%CHARACTERSET’;。发现是AL32UTF8。检查客户端运行SQLLDR的环境的NLS_LANG设置echo $NLS_LANGLinux或set NLS_LANGWindows。发现是AMERICAN_AMERICA.WE8MSWIN1252。根因SQLLDR作为客户端工具其读取数据文件时使用的字符集由环境变量NLS_LANG决定。如果NLS_LANG是单字节字符集如WE8MSWIN1252而文件是UTF-8那么多字节的UTF-8字符如中文就会被错误解读导致乱码。解决方案在运行SQLLDR之前设置客户端的NLS_LANG与数据文件编码一致。对于UTF-8文件应设置为export NLS_LANGAMERICAN_AMERICA.AL32UTF8 # Linux set NLS_LANGAMERICAN_AMERICA.AL32UTF8 # Windows CMD或者更彻底的办法是在生成CSV文件时就确保其编码与数据库字符集一致。这个坑告诉我字符集问题在数据迁移中永远是优先级最高的排查点之一尤其是在跨平台、跨环境操作时。