使用sqlldr工具导入oracle数据库方法

关注“IT实战基地”，与行业大咖交流学习！

前言

近期有客户反馈在使用PL/SQL客户端工具批量导入数据时效率较低（外部数据导入的格式为csv文件格式，数据有百万及千万行级），在进行数据批量处理的过程中经常造成数据库运行缓慢，影响数据库性能，且作业时间过长，影响业务办理。客户期望我们能够想想其他办法提高数据批量处理效率以确保业务正常使用。

我们都知道在 Oracle 数据库中，外部数据导入时通常会采用以下几种方法：

1. 建立数据库间的 dblink，然后用 create table B as select * from A@dblink where ...，或 insert into B select * from A@dblink where ...；

2. expdp A表，再impdp到B表，expdp时可加查询条件；

3. 程序或数据库管理实现 select from A ..，然后 insert into B ...，也要分批提交，如PL/SQL，TOAD 等；

4. 除了以上方法外，还有一种就是使用 Sql Loader(sqlldr) 来进行外部数据的导入，SQL*Loader是oracle自带的高速批量数据加载工具。这是一个非常有用的工具，可从多种平面文件格式向Oracle数据库中加载数据，QLLDR可以在极短的时间内加载数量庞大的数据，效率要比insert into要高的多。

下面我们来详细了解下如何使用SQL*Loader 来进行外部数据导入。

◆◆

数据导入前准备

◆◆

1.分析导出文件内容

可以看出，该csv文件内容大约有170万行数据，每行数据有5个值，使用逗号隔开，双引号区分各个值，并且导出的字符集为UTF-8。

2.根据数据结构建立表

CREATE TABLE ldzk.mailtable(

mail VARCHAR2(50),

cn VARCHAR2(500),

idcardtype VARCHAR(255),

idcardnumber VARCHAR2(50),

telephonenumber VARCHAR2(50));

（此处因客户目标仅打算导入为中间表，不涉及生产表，故表结构比较简单。）

◆◆

创建导入脚本

◆◆

1.导入脚本详解

我们需要创建一个loader_control.ctl的文本。

LOAD DATA

CHARACTERSETUTF8--导出文件的字符集，以及导入目标字符集

INFILE 'mail-export.csv' --引用数据文件

BADFILE'badfile.txt' --符合条件但导入失败，坏数据文件

DISCARDFILE 'discardfile.txt' --不符合条件且导入失败，丢弃数据文件

INTO TABLEldzk.mailtable--插入的表名(insert into 默认缺省，要求表为空；append into 表中追加记录；replace 删除旧记录；truncate同上)

Fields terminated by","--列分隔符号

Optionally enclosed by'"'--值分隔符号,没有则可不选，此处是2个单引号括住1个双引号

trailing nullcols --表示如表的结尾字段没有对应的值时允许为空

(mail,cn, idcardtype,idcardnumber,telephonenumber) --对应表列名

2.确认导入的脚本及数据文件权限

3.开始导入

sqlldr system/password@demo control="loader_control.ctl" log="loader_log.log" parallel=true

--可选参数

log -- 记录导入时的日志文件，默认为 控制文件(去除扩展名).log

errors -- 允许的错误记录数，可以用他来控制一条记录都不能错

rows -- 多少条记录提交一次，默认为 64

skip -- 跳过的行数，比如导出的数据文件前面几行是表头或其他描述

parallel -- do parallel load （并行导入） (默认FALSE)

完成对1786383行的读操作（此处不一定能完成1786383行的写入，有可能出现问题数据）

◆◆

检验数据

◆◆

1. 查看在脚本配置文件中的日志文件：loader_log.log

发现完成1786374行写入，出现9行问题数据，花费1:55秒。（如果想优化速度可以使用并行和提交行数多少来提高性能）

2.查看Badfile，里面记录了导入失败的数据。

导入失败的原因在Logifle里面有记录，分别是列的最大字数限制、导入格式不对（多了一个值）；

该9行数据最后由手工方式导入。

Oracle切换中文LANG下进行查询cn字段的中文也没有出现乱码，导入成功。

结论

在平时工作中协助客户对数据库进行导入数据时，往往不能指定客户导出的文件格式，常见的都为此类的csv和dmp文件格式，当使用客户端工具PL/SQL或者其他工具批量导入数据发现缓慢时，可以考虑使用sqlldr工具进行导入。

最值得注意的是字符集的问题，以及对应csv文件的格式。比如此次使用UTF-8，如果使用其他的字符集则可能出现编号510错误，工具不识别回车符号，将170万行只看作一行，工具提示对应的列字段不够大，但这其实是字符集的问题。使用sqlldr工具，导入的行数越多效率也就越明显。平时建议可以多使用该方式导入数据。

现在，你学会了吗？如对该经验分享有疑问，可进入小程序进行技术交流，技术人员之间互相讨论，并且会有专门的技术专家答疑解惑。

▼