使用Sqlloader处理数据

Oracle数据导出工具sqluldr2可以将数据以csv、txt等文件格式导出，适用于大批量数据的导出，导出速度非常快，导出后可以使用Oracle SQL Loader工具将数据导入到数据库中。下面将介绍Sqluldr2和sqlldr在Windows平台下的数据处理过程。

一、获取软件

sqluldr24.0.1版本oracle数据导出工具-数据库文档类资源-CSDN下载

下载完sqluldr解压后，文件夹内容如下：

sqluldr2.exe 用于32位windows平台；

sqluldr2_linux32_10204.bin 适用于linux32位操作系统；

sqluldr2_linux64_10204.bin 适用于linux64位操作系统；

sqluldr264.exe 用于64位windows平台。

二、sqluldr2 使用方法

1、首先将sqluldr2.exe复制到执行目录下，即可开始使用

2、查看help 帮助

E:\BaiduYunDownload>sqluldr264.exeSQL*UnLoader: Fast Oracle Text Unloader (GZIP, Parallel), Release 4.0.1
(@) Copyright Lou Fangxin (AnySQL.net) 2004 - 2010, all rights reserved.License: Free for non-commercial useage, else 100 USD per server.Usage: SQLULDR2 keyword=value [,keyword=value,...]Valid Keywords:user    = username/password@tnsnamesql     = SQL file namequery   = select statementfield   = separator string between fieldsrecord  = separator string between recordsrows    = print progress for every given rows (default, 1000000)file    = output file name(default: uldrdata.txt)log     = log file name, prefix with + to append modefast    = auto tuning the session level parameters(YES)text    = output type (MYSQL, CSV, MYSQLINS, ORACLEINS, FORM, SEARCH).charset = character set name of the target database.ncharset= national character set name of the target database.parfile = read command option from parameter filefor field and record, you can use '0x' to specify hex character code,\r=0x0d \n=0x0a |=0x7c ,=0x2c, \t=0x09, :=0x3a, #=0x23, "=0x22 '=0x27

三、执行数据导出命令

常规导出

sqluldr264 test/123456@kyy query="select * from task where task_id in (1901,1951,2001,2101)" quote=\" head=yes file=task.csv

说明：

field：分隔符，指定字段分隔符，默认为逗号；

record：分隔符，指定记录分隔符，默认为回车换行，Windows下的换行；

quote：引号符，指定非数字字段前后的引号符；

charset：字符集，执行导出时的字符集，一般有UTF8、GBK等；

head：表头，head=no 不导出表头；head=yes 导出表头。

例如现在要改变默认的字段分隔符，用“#”来分隔记录，导出的命令如下所示：

sqluldr2 test/test sql=tmp.sql field=#

在指定分隔符时，可以用字符的ASCII代码（0xXX，大写的XX为16进制的ASCII码值）来指定一个字符，常用的字符的ASCII代码如下：

回车=0x0d，换行=0x0a，TAB键=0x09，|=0x7c，&=0x26，双引号=0x22，单引号=0x27

在选择分隔符时，一定不能选择会在字段值中出现的字符组合，如常见的单词等，很多次导入时报错，回过头来找原因时，都发现是因为分隔符出现在字段值中了。

执行示例：

E:\BaiduYunDownload>sqluldr264 test/123456@kyy query="select * from PDSREPORTVALIDATE where task_status_id in (select task_stat_id from task_stat where task_id in (1901,1951,2001,2101) and report_id=6 and member_id in (3323,3976,5997,5998,5322,5323,5325,5326,5647,5648) and status='approved')" table=PDSREPORTVALIDATE quote=\" head=yes file=PDSREPORTVALIDATE.csv0 rows exported at 2018-11-29 10:47:01, size 0 MB.1000000 rows exported at 2018-11-29 11:02:56, size 1060 MB.1121292 rows exported at 2018-11-29 11:05:08, size 1200 MB.output file PDSREPORTVALIDATE.csv closed at 1121292 rows, size 1200 MB.

使用sql参数

sqluldr2 test/test@127.0.1.1/orcl sql=test_sql.sql head=yes file=d:\tmp001.csv

test_sql的内容为：

select * from temp_001

使用log参数

当集成sqluldr2在脚本中时，希望不输出上述打印信息，但又希望这些信息能保留，这时可以用“LOG”选项来指定日志文件名。

sqluldr2 test/test@127.0.1.1/orcl sql=test_sql.sql head=yes file=d:\tmp001.csv log=+d:\tmp001.log

+号表示追加模式

使用 table 参数

当使用table参数时，在目录下会生成对应的ctl控制文件，如下语句会生成temp_001_sqlldr.ctl文件。

sqluldr2 test/test@127.0.1.1/orcl query="select * from temp_001" table=temp_001 head=yes file=d:\tmp001.csv

控制文件示例：

--
-- SQL*UnLoader: Fast Oracle Text Unloader (GZIP), Release 3.0.1
-- (@) Copyright Lou Fangxin (AnySQL.net) 2004 - 2010, all rights reserved.
--
--  CREATE TABLE task (
--    TASK_ID NUMBER(16),
--    START_TIME TIMESTAMP,
--    END_TIME TIMESTAMP,
--    NAME VARCHAR2(360),
--    STATUS VARCHAR2(30),
--    VERSION VARCHAR2(128),
--    REPORT_PERIOD NUMBER(5),
--    IS_ENABLED VARCHAR2(5),
--    CREATED TIMESTAMP,
--    UPDATED TIMESTAMP,
--    DELETED TIMESTAMP,
--    DESCRIPTION VARCHAR2(360),
--    ROLES VARCHAR2(1024),
--    DATA_VALIDITY_START TIMESTAMP,
--    DATA_VALIDITY_END TIMESTAMP,
--    IS_LOCK VARCHAR2(1),
--    IS_REPORT_REMIND VARCHAR2(1),
--    LOCK_TIME TIMESTAMP,
--    LOCK_HOST VARCHAR2(30),
--    IS_AUTO_AUDIT VARCHAR2(1),
--    AUTO_AUDIT_DAY NUMBER(16),
--    CATEGORY NUMBER(38),
--    NEW_START_TIME TIMESTAMP,
--    NEW_END_TIME TIMESTAMP,
--    NEW_STATUS VARCHAR2(30)
--  );
--
OPTIONS(BINDSIZE=2097152,READSIZE=2097152,SKIP=1,ERRORS=-1,ROWS=50000)
LOAD DATA
INFILE 'task.csv' "STR X'0a'"
INSERT INTO TABLE task
FIELDS TERMINATED BY X'2c' OPTIONALLY ENCLOSED BY '"'  TRAILING NULLCOLS 
("TASK_ID" CHAR(18) NULLIF "TASK_ID"=BLANKS,"START_TIME" TIMESTAMP "YYYY-MM-DD HH24:MI:SSXFF" NULLIF "START_TIME"=BLANKS,"END_TIME" TIMESTAMP "YYYY-MM-DD HH24:MI:SSXFF" NULLIF "END_TIME"=BLANKS,"NAME" CHAR(360) NULLIF "NAME"=BLANKS,"STATUS" CHAR(30) NULLIF "STATUS"=BLANKS,"VERSION" CHAR(128) NULLIF "VERSION"=BLANKS,"REPORT_PERIOD" CHAR(7) NULLIF "REPORT_PERIOD"=BLANKS,"IS_ENABLED" CHAR(5) NULLIF "IS_ENABLED"=BLANKS,"CREATED" TIMESTAMP "YYYY-MM-DD HH24:MI:SSXFF" NULLIF "CREATED"=BLANKS,"UPDATED" TIMESTAMP "YYYY-MM-DD HH24:MI:SSXFF" NULLIF "UPDATED"=BLANKS,"DELETED" TIMESTAMP "YYYY-MM-DD HH24:MI:SSXFF" NULLIF "DELETED"=BLANKS,"DESCRIPTION" CHAR(360) NULLIF "DESCRIPTION"=BLANKS,"ROLES" CHAR(1024) NULLIF "ROLES"=BLANKS,"DATA_VALIDITY_START" TIMESTAMP "YYYY-MM-DD HH24:MI:SSXFF" NULLIF "DATA_VALIDITY_START"=BLANKS,"DATA_VALIDITY_END" TIMESTAMP "YYYY-MM-DD HH24:MI:SSXFF" NULLIF "DATA_VALIDITY_END"=BLANKS,"IS_LOCK" CHAR(1) NULLIF "IS_LOCK"=BLANKS,"IS_REPORT_REMIND" CHAR(1) NULLIF "IS_REPORT_REMIND"=BLANKS,"LOCK_TIME" TIMESTAMP "YYYY-MM-DD HH24:MI:SSXFF" NULLIF "LOCK_TIME"=BLANKS,"LOCK_HOST" CHAR(30) NULLIF "LOCK_HOST"=BLANKS,"IS_AUTO_AUDIT" CHAR(1) NULLIF "IS_AUTO_AUDIT"=BLANKS,"AUTO_AUDIT_DAY" CHAR(18) NULLIF "AUTO_AUDIT_DAY"=BLANKS,"CATEGORY" CHAR(40) NULLIF "CATEGORY"=BLANKS,"NEW_START_TIME" TIMESTAMP "YYYY-MM-DD HH24:MI:SSXFF" NULLIF "NEW_START_TIME"=BLANKS,"NEW_END_TIME" TIMESTAMP "YYYY-MM-DD HH24:MI:SSXFF" NULLIF "NEW_END_TIME"=BLANKS,"NEW_STATUS" CHAR(30) NULLIF "NEW_STATUS"=BLANKS
)

四、sqlldr 使用方法

查看sqlldr 的帮助文档

sqlldr工具可以将文本数据导入到数据库表中，这个是oracle自带的工具，可直接使用。

D:\>sqlldrSQL*Loader: Release 11.2.0.1.0 - Production on 星期五 11月 30 13:49:22 2018Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.用法: SQLLDR keyword=value [,keyword=value,...]有效的关键字:userid -- ORACLE 用户名/口令control -- 控制文件名log -- 日志文件名bad -- 错误文件名data -- 数据文件名discard -- 废弃文件名
discardmax -- 允许废弃的文件的数目         (全部默认)skip -- 要跳过的逻辑记录的数目  (默认 0)load -- 要加载的逻辑记录的数目  (全部默认)errors -- 允许的错误的数目         (默认 50)rows -- 常规路径绑定数组中或直接路径保存数据间的行数(默认: 常规路径 64, 所有直接路径)bindsize -- 常规路径绑定数组的大小 (以字节计)  (默认 256000)silent -- 运行过程中隐藏消息 (标题,反馈,错误,废弃,分区)direct -- 使用直接路径                     (默认 FALSE)parfile -- 参数文件: 包含参数说明的文件的名称parallel -- 执行并行加载                    (默认 FALSE)file -- 要从以下对象中分配区的文件
skip_unusable_indexes -- 不允许/允许使用无用的索引或索引分区  (默认 FALSE)
skip_index_maintenance -- 没有维护索引, 将受到影响的索引标记为无用  (默认 FALSE)
commit_discontinued -- 提交加载中断时已加载的行  (默认 FALSE)readsize -- 读取缓冲区的大小               (默认 1048576)
external_table -- 使用外部表进行加载; NOT_USED, GENERATE_ONLY, EXECUTE  (默认 NOT_USED)
columnarrayrows -- 直接路径列数组的行数  (默认 5000)
streamsize -- 直接路径流缓冲区的大小 (以字节计)  (默认 256000)
multithreading -- 在直接路径中使用多线程resumable -- 启用或禁用当前的可恢复会话  (默认 FALSE)
resumable_name -- 有助于标识可恢复语句的文本字符串
resumable_timeout -- RESUMABLE 的等待时间 (以秒计)  (默认 7200)
date_cache -- 日期转换高速缓存的大小 (以条目计)  (默认 1000)
no_index_errors -- 出现任何索引错误时中止加载  (默认 FALSE)PLEASE NOTE: 命令行参数可以由位置或关键字指定
。前者的例子是 'sqlldr
scott/tiger foo'; 后一种情况的一个示例是 'sqlldr control=foo
userid=scott/tiger'。位置指定参数的时间必须早于
但不可迟于由关键字指定的参数。例如,
允许 'sqlldr scott/tiger control=foo logfile=log', 但是
不允许 'sqlldr scott/tiger control=foo log', 即使
参数 'log' 的位置正确。

创建测试文件

在D盘根目录下创建测试文件tmp_insert.txt ，其内容如下：

PROD_ID,ACC_NUM
18283918,18762535162
12361527,18482762831

创建控制文件

创建tmp_insert01.ctl内容如下：

options(direct=true,errors=10000000,skip=1) 
load data 
characterset zhs16gbk
into table temp_001 truncate
fields terminated by ',' optionally enclosed by '"'
trailing nullcols
( prod_id   "trim(:prod_id)",acc_num "trim(:acc_num)"
)

参数说明：

characterset：字符集，一般使用字符集AL32UTF8，如果出现中文字符集乱码时，改成 ZHS16GBK。

fields terminated by 'string'：文本列分隔符。

当为tab键时，改成'\t'，或者 X'09'；

空格分隔符 whitespace，换行分隔符 '\n' 或者 X'0A'；

回车分隔符 '\r' 或者 X'0D'；默认为'\t'。

optionally enclosed by 'char'：字段包括符。当为 ' ' 时，不把字段包括在任何引号符号中；当为 "'" 时，字段包括在单引号中；当为'"'时，字段在包括双引号中；默认不使用引用符。

fields escaped by 'char'：转义字符，默认为'\'。

trailing nullcols：表字段没有对应的值时，允许为空。

insert：为缺省方式，在数据装载开始时要求表为空；

append：在表中追加新记录；

replace：删除旧记录，替换成新装载的记录；

truncate：先清空表，再添加记录；

skip=1：表示插入数据时，跳过第一行(标题)，从第二行开始导入；

执行数据导入

sqlldr test/test@127.0.1.1/orcl data=d:\tmp_insert.txt control=d:\tmp_insert01.ctl log=d:\tmp_insert01.log bad=d:\tmp_insert01.bad

这里也可以不指定log、bad，目录下会自动生成log和bad文件。

五、常见错误解决

1、SQL*Loader-601: For INSERT option, table must be empty.

这时需要，更改为追加(append)方式加载数据。

2、记录 3: 被拒绝 - 表 MANAGER 的列 REMARK 出现错误。

TERMINATED 和 ENCLOSED 字段后没有终止定界符

中文编码问题导致，导出时如果加了charset=UTF8，遇到中文可能出现此问题。去掉charset参数。

3、ROWS 参数所用的值已从 50000 更改为 1304

记录 551971: 被拒绝 - 表 ITEM_USE_INFO_REPORT 的列 "SUBJECT_NAME" 出现错误。

没有第二个定界字符串

数据中可能存在换行符号或其它特殊符号导致，需要去掉原始数据的特殊符号。

记录 554501: 被拒绝 - 表 ITEM_USE_INFO_REPORT 的列 "ITEM_USE_INFO_REPORT_ID" 出现错误。

ORA-01722: 无效数字

行错乱，导致读到错误的数据类型。

4、./sqluldr2.bin: error while loading shared libraries: libclntsh.so.10.1: cannot open shared object file: No such file or directory

find / -name libclntsh.so.10.1

vi /etc/ld.so.conf

/opt/oracle/product/OraHome/lib/

ldconfig

5、ORA-24345: A Truncation or null fetch error occurred

select * from nls_database_parameters where parameter ='NLS_CHARACTERSET';

加上safe=yes charset=ZHS16GBK