sqlldr 用法及问题汇总

1. 语法和参数

Keyword	默认值	描述
userid		ORACLE 用户名/口令
control		控制文件名
log		日志文件名
bad		错误文件名
data		数据文件名
discard		废弃文件名
discardmax	全部	允许废弃的文件的数目
skip	0	要跳过的逻辑记录的数目
load	全部	要加载的逻辑记录的数目
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	使用外部表进行加载; NOT_USED, GENERATE_ONLY, EXECUTE
columnarrayrows	5000	直接路径列数组的行数
streamsize	256000	直接路径流缓冲区的大小 (以字节计)
multithreading		在直接路径中使用多线程
resumable	FALSE	启用或禁用当前的可恢复会话
resumable_name		有助于标识可恢复语句的文本字符串
resumable_timeout	7200	RESUMABLE 的等待时间 (以秒计)
date_cache	1000	日期转换高速缓存的大小 (以条目计)

 

2.范例

2.1 处理数据文件

首先了解.csv文件：逗号分隔值（Comma-Separated Values，CSV，有时也称为字符分隔值，因为分隔字符也可以不是逗号），其文件以纯文本形式存储表格数据（数字和文本）。我们将数据文件进行两处理和一观察：

两处理：
处理表：删除表头、处理换行数据（文件内查找替换ctrl+j 为excl 默认换行符键入方式）
处理文件格式：转换为csv文件
一观察：
观察记录比表结构，记录行数便于后续核对

2.2 编写建表语句

create table shenji.tsqdmx_201912(       // 用户.表名
SLRQ       varchar(255),                 //表结构要和表头对应齐（命名要求因地制宜）
GDRQ       varchar(255),
GDLSH      varchar(255),
SLSJ       varchar(255),
SLHM       varchar(255),
KHGSD      varchar(255),
FWQQLX     varchar(1500),                // var最大默认值4000，根据表内容决定
SLNR       clob,                         //  长文本已clob类型接入
GDSJ       varchar(255),
GDYJ       varchar(4000),
GDCL       clob,
SCPFBM     varchar(255)
);

注意： 建表前复核目标库是否正确！

2.3 编写控制文件

vi tsqdmx_201912.ctl  
load data                                                                                                              
characterset ZHS16GBK                                //字符集确认                                                             
infile '/home/oracle/sqlldr/tsqdmx_201912.csv'     // 导入文件                                                                         
append into table shenji.tsqdmx_201912            //接入表的方式                                        
fields terminated by ','                         // 数据分隔方式
optionally enclosed by '"'                       //数据包裹方式                                                                     
trailing nullcols                                                                                                     
(SLRQ,GDRQ,GDLSH,SLSJ,SLHM,KHGSD,FWQQLX char(1500),SLNR char(10000),GDSJ,GDYJ char(4000),GDCL char(30000),SCPFBM)                              // 列值属性确认特殊要标注处理

/****一，要加载的数据文件。
LOAD[DATA]
[ {INFILE | INDDN } {file | * }
--INFILE 和INDDN是同义词，它们后面都是要加载的数据文件。如果用 * 则表示数据就在控制文件内。[STREAM | RECORD | FIXED length [BLOCKSIZE size]|VARIABLE [length] --STRAM 表示一次读一个字节的数据。--RECORD 使用宿主操作系统文件及记录管理系统。--FIXED length 要读的记录长度为length字节。--VARIABLE 被读的记录中前两个字节包含的长度，length 记录可能的长度。默认为8k 字节。]
[{ BADFILE | BADDN } file ]--BADFILE和BADDN同义。Oracle 不能加载数据到数据库的那些记录。
[{DISCARDS | DISCARDMAX} integr ]--DISCARDFILE和DISCARDDN是同义词。记录没有通过的数据。--DISCARDS和DISCARDMAX是同义词。Integer 为最大放弃的文件个数。******/

/****二，加载方法
[{INDDN | INFILE} . . . ]
[APPEND | REPLACE | INSERT ]--APPEND 给表添加行。--INSERT 给空表增加行（如果表中有记录则退出）。--REPLACE（truncate） 先清空表在加载数据。
 
[RECLENT integer]--RECLEN 用于两种情况:     1）SQLLDR不能自动计算记录长度，     2）用户想看坏文件的完整记录时。     对于后一种，Oracle只能按常规把坏记录部分写到错误的地方。如果看整条记录，则可以将整条记录写到坏文件中。*****/

/*三，指定最大的记录长度*/--CONCATENATE 指定最大的记录长度,允许用户设定一个整数，表示要组合逻辑记录的数目。
 
/* 四，建立逻辑记录：*/
[ { CONCATENATE integer |CONTINUEIF { [THIS | NEXT] (start[: end])LAST } Operator { 'string' | X 'hex' } } ]
--THIS 检查当前记录条件，如果为真则连接下一个记录。--NEXT 检查下一个记录条件。如果为真，则连接下一个记录到当前记录来。--start: end 表示要检查在THIS或NEXT字串是否存在继续串的列，以确定是否进行连接。
 
/*五，指定要加载的表：*/INTO TABLE [user.]table--INTO TABLE 要加的表名。
[APPEND | REPLACE|INSERT]
[WHEN condition [AND condition]...]
 
/*六，介绍并括起记录中的字段*/
[FIELDS [delimiter] ]
(
column {RECNUM | CONSTANT value |SEQUENCE ( { integer | MAX |COUNT} [, increment] ) |[POSITION ( { start [end] | * [ + integer] }) ]
datatype
   [TERMINATED [ BY ] {WHITESPACE| [X] 'character' } ]
   [[OPTIONALLY] ENCLOSE  [BY] [X]'charcter']
[NULLIF condition ]
[DEFAULTIF condotion]
}-- FIELDS  给出记录中字段的分隔符，　FIELDS格式为：FIELDS [TERMIALED [BY] {WHITESPACE | [X] 'charcter'} ]  [ [ OPTIONALLY] ENCLOSE [BY] [X]'charcter' ]  TERMINATED 读完前一个字段即开始读下一个字段直到结束。  WHITESPACE 是指结束符是空格的意思。包括空格、Tab、换行符、换页符及回车符。如果是要判断单字符，可以用单引号括起，如X'1B'等。  OPTIONALLY ENCLOSED 表示数据应由特殊字符括起来。也可以括在TERMINATED字符内。使用OPTIONALLY要同时用TERMINLATED。  ENCLOSED 指两个分界符内的数据。如果同时用 ENCLOSED和TERMINAED ，则它们的顺序决定计算的顺序。

3.导入

sqlldr userid=/ control=tsqdmx_201912.ctl bindsize=256000000 readsize=1048576000

SQLLDR使用说明

• 命令使用

sqlldr username/password@sid control=*.ctl log=****.log

- 参数说明

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: 命令行参数可以由位置或关键字指定。前者的例子是 'sqlldrscott/tiger foo'; 
后一种情况的一个示例是 'sqlldr control=foouserid=scott/tiger'。
位置指定参数的时间必须早于
但不可迟于由关键字指定的参数。例如,允许 'sqlldr scott/tiger control=foo logfile=log', 
但是不允许 'sqlldr scott/tiger control=foo log', 即使参数 'log' 的位置正确。

• .ctl 文件说明

load data  
infile "d://test.txt"    外部数据文件  
infile "d://test1.txt"   可指定多个数据文件  
insert into table test   向表中插入数据  
fields terminated by ","    外部文件的数据以“，”分隔  
OPTIONALLY ENCLOSED BY '"'  部分字段可以用双引号包起来  
trailing nullcols        表中的字段没有对应的值时填充空值  
(  
id integer external,    integer external 表示插入的数据是string，如果只保留integer，表示插入的数据是二进制  
name "upper(:name)",     将插入的值转换为大写  
con ":id||:name",        表中CON列的值是ID和NAME的组合值  
dt date"yyyy-mm-dd" ,     插入日期型数据  
)

• 创建表

可以根据csv中字段类型创建对应导入表

create table xxx(
id number(28),
name nvarchar2(2000)
)

问题1 Field in data file exceeds maximum length

• 问题原因 1.表中字段设置过小

• 解决方法 字段扩长

• 问题原因 2. Sqlldr中默认char类型最大为255 超出255的char类型数据需要在ctl文件中指明长度

#此处 指定name为char(2000) ,需要注意的是sqlldr中 char varchar2 nvarchar2等字符类型的数据列指定类型时 统一都指定为char

load data  
infile "d://test.txt"    外部数据文件  
infile "d://test1.txt"   可指定多个数据文件  
insert into table test   向表中插入数据  
fields terminated by ","    外部文件的数据以“，”分隔  
OPTIONALLY ENCLOSED BY '"'  部分字段可以用双引号包起来   
(  
id integer external,    integer external 表示插入的数据是string，如果只保留integer，表示插入的数据是二进制  
name "upper(:name)",     将插入的值转换为大写  
con ":id||:name",        表中CON列的值是ID和NAME的组合值  
name char(2000) ,     插入日期型数据  
)

问题2 table *** not empty

• 解决方案

• 1.使用 truncate table 语句截断表
• 2.使用append into table 使用追加模式写入

infile "d://test.txt"    外部数据文件  
infile "d://test1.txt"   可指定多个数据文件  
append into table test   向表中插入数据  
fields terminated by ","    外部文件的数据以“，”分隔  
OPTIONALLY ENCLOSED BY '"'  部分字段可以用双引号包起来   
(  
id integer external,    integer external 表示插入的数据是string，如果只保留integer，表示插入的数据是二进制  
name "upper(:name)",     将插入的值转换为大写  
con ":id||:name",        表中CON列的值是ID和NAME的组合值  
dt date"yyyy-mm-dd"      插入日期型数据  
)

问题3 空值插入

• ctl文件中指定使用 trailing nullcols 选项

load data  
infile "d://test.txt"    外部数据文件  
infile "d://test1.txt"   可指定多个数据文件  
insert into table test   向表中插入数据  
fields terminated by ","    外部文件的数据以“，”分隔  
OPTIONALLY ENCLOSED BY '"'  部分字段可以用双引号包起来  
trailing nullcols        表中的字段没有对应的值时填充空值  
(  
id integer external,    integer external 表示插入的数据是string，如果只保留integer，表示插入的数据是二进制  
name "upper(:name)",     将插入的值转换为大写  
con ":id||:name",        表中CON列的值是ID和NAME的组合值  
dt date"yyyy-mm-dd" ,     插入日期型数据  
)

sqlldr 直接路径加载direct=true的副作用

direct=true的官方解释

使用直接路径加载：direct=true。直接加载全部记录，提高速度。但是此方法有个副作用，就是会使索引失效。mos上面也有说明，根据文章

Common Maintenance Commands That Cause Indexes to Become Unusable (Doc ID 165917.1)的描述：

索引失效的两种原因：
1）sqlldr 【sqlldr ( parallel or direct ) append 】【sqlldr direct=true + 主键重复(append)】
1）sqlldr 【direct=true && skip_index_maintenance=true】

采用direct方式，虽然可以提高速度，但是有可能造成索引失效。加载成功后，检查结果时，不能只看sqlldr的log中，还要检查目标表上唯一索引的status

使用sqlldr批量导入多个文件

问题分析

首先我们想到的是sqlldr命令，它是Oracle自带的数据加载工具，也是大型数据仓库加载数据的常用方法，可提供快速的数据导入途径(direct,parallel)。

与客户沟通后，本次导入主要涉及下面几个问题：

1. 文件比较多，大约400个
2. 导入时需要解析文件名，转换成[日期]和[类型]两个字段
3. 文件字符集为latin1，目标数据库字符集为gbk
4. 文件中可能存在格式错误的行(比如少分隔符)，这些行要记录到Error表中
5. 部分字段值要做转换，比如转换成日期类型
6. 部分字段要赋默认值

分析完导入表和文件格式后，决定使用shell+iconv+sqlldr来解决这些问题，因为shell可以解决1、2，iconv可以解决3，sqlldr可以解决4、5、6。

1. sqlldr工具的使用：

sqlldr参数查看：

#linux终端下输入sqlldr，回车
su - oracle
sqlldr

SQL*Loader: Release 11.2.0.1.0 - Production on Tue Aug 16 21:05:28 2022

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


Usage: SQLLDR keyword=value [,keyword=value,...]

Valid Keywords:

    userid -- ORACLE username/password           
   control -- control file name                  
       log -- log file name                      
       bad -- bad file name                      
      data -- data file name                     
   discard -- discard file name                  
discardmax -- number of discards to allow          (Default all)
      skip -- number of logical records to skip    (Default 0)
      load -- number of logical records to load    (Default all)
    errors -- number of errors to allow            (Default 50)
      rows -- number of rows in conventional path bind array or between direct path data saves
               (Default: Conventional path 64, Direct path all)
  bindsize -- size of conventional path bind array in bytes  (Default 256000)
    silent -- suppress messages during run (header,feedback,errors,discards,partitions)
    direct -- use direct path                      (Default FALSE)
   parfile -- parameter file: name of file that contains parameter specifications
  parallel -- do parallel load                     (Default FALSE)
      file -- file to allocate extents from      
skip_unusable_indexes -- disallow/allow unusable indexes or index partitions  (Default FALSE)
skip_index_maintenance -- do not maintain indexes, mark affected indexes as unusable  (Default FALSE)
commit_discontinued -- commit loaded rows when load is discontinued  (Default FALSE)
  readsize -- size of read buffer                  (Default 1048576)
external_table -- use external table for load; NOT_USED, GENERATE_ONLY, EXECUTE  (Default NOT_USED)
columnarrayrows -- number of rows for direct path column array  (Default 5000)
streamsize -- size of direct path stream buffer in bytes  (Default 256000)
multithreading -- use multithreading in direct path  
 resumable -- enable or disable resumable for current session  (Default FALSE)
resumable_name -- text string to help identify resumable statement
resumable_timeout -- wait time (in seconds) for RESUMABLE  (Default 7200)
date_cache -- size (in entries) of date conversion cache  (Default 1000)
no_index_errors -- abort load on any index errors  (Default FALSE)

参数解释：

• userid 登陆账号/密码，还可以指定登陆的Oracle实例信息
• control 文本字段解析与表的字段映射关系，在此文件中配置
• data 要导入的文本文件
• bad 保存导入时出错的行
• log 日志文件：记录出错原因，导入成功及失败的行数，耗时等
• errors 导入时出错的行数大于此值，就终止此导入(默认50)
• rows 扫描多少行保存一次数据(常规路径默认64,直接路径默认全部)，假如设置为1000，则每1000行打印一条保存信息
• bindsize 常规路径绑定数组的大小(默认256KB)
• direct 使用直接路径(默认false)
• parallel 执行并行加载(默认false)

sqlldr导入命令：

sqlldr \
userid=账号/密码@实例IP:1521/orcl \
control=/tmp/wyl/my.ctl \
direct=true \
errors=1000000 \
data=/tmp/wyl/data/AAAA_20220816.TXT \
bad=/tmp/wyl/bad/AAAA_20220816.bad \
log=/tmp/wyl/log/AAAA_20220816.log

sqlldr控制文件：

OPTIONS (skip=1)
load data
characterset ZHS16GBK
append into table "TABLE_WYL"
fields terminated by "|+|"
optionally enclosed by '"'
trailing nullcols
(
    "ROWNUM" FILLER,
    "CREATE_TIME" EXPRESSION "TO_DATE('{CREATE_TIME}','YYYYMMDD')",
    "TRADE_TYPE" CONSTANT "{TRADE_TYPE}",
    "RECORD_NUM",
    "MER_NAME",
    "TRAN_DATE" TIMESTAMP(6) WITH TIME ZONE "YYYY-MM-DD\"T\"HH24:MI:SS.FF3 TZR",
    "FILE_NAME" CONSTANT "{FILE_NAME}"
)

参数解释：

• skip=1 跳过前N行，一般用于不导入第一行的字段名
• characterset 指定要导入的文件字符集
• append 数据追加到一张表。可选项：insert(默认)，append，replace, truncate
• fields terminated by 指定字段分隔符
• optionally enclosed by 解析用指定字符包围的字段，比如：a,“b"c”,d，第2个字段解析成：b"c
• trailing nullcols 表中的字段没有对应的列时，允许为空

字段类型：

• FILLER 该列不导入表中
• DATE 需要使用EXPRESSION和TO_DATE函数把字符串转换成日期
• TIMESTAMP 带时区的TIMESTAMP类型需要指定解析格式
• CONSTANT 指定固定字符串
• 未指定类型 只写了字段名的字段，默认为VARCHAR类型

2. 字符集转换
本次要把latin1字符集转换成GBK字符集，正确的做法是，使用GBK字符集打开文本（中文正常），然后另存为GBK文件：

#查看字符集
file -i AAAA_20220816.TXT
#vim查看
:set fileencoding

#latin1转换GBK
iconv -f GBK AAAA_20220816.TXT -o AAAA_20220816_R.TXT

3. 批量导入

#!/bin/bash

ctl_file_templet=/tmp/wyl/my.ctl  #控制文件模板
data_dir=/tmp/wyl/data/   #数据文件目录

ctl_dir=/tmp/wyl/ctl/   #每个文件对应的ctl目录
bad_dir=/tmp/wyl/bad/   #每个文件对应的bad目录
log_dir=/tmp/wyl/log/   #每个文件对应的log目录

for data_file in ${data_dir}*.TXT
do
    file=${data_file##*/}
    file_no_suffix=${file%%.*}

    ctl_file=${ctl_dir}${file_no_suffix}".ctl"
    bad_file=${bad_dir}${file_no_suffix}".bad"
    log_file=${log_dir}${file_no_suffix}".log"

    create_time=${file_no_suffix##*_}
    trade_type=${file_no_suffix:0:4}
    trade_type2="99"
    if [ "$trade_type" == "AAAA" ]; then
        trade_type2="1"
    elif [ "$trade_type" == "BBBB" ]; then
        trade_type2="0"
    fi

    cat $ctl_file_templet | sed "s/{CREATE_TIME}/${create_time}/g" | sed "s/{TRADE_TYPE}/${trade_type2}/g" | sed "s/{FILE_NAME}/${file_no_suffix}/g" > $ctl_file

    /data/oracle/product/11.2.0/db_1/bin/sqlldr \
    userid=账号/密码@实例IP:1521/orcl \
    control=$ctl_file \
    direct=true \
    errors=1000000 \
    data=$data_file \
    bad=$bad_file \
    log=$log_file
done

相关说明：

• 每导入一个文本，重新生成ctl文件，因为要替换ctl中某些字段的常量值
• 每导入一个文本，生成对应的bad和log文件
• bad文件中的错误记录，最后由程序统一处理

解决方案

此次主要解决客户"快速导入数据"的需求，故sqlldr是首选。方案思路如下：

1. 使用sqlldr是否能"快速导入数据"？YES
2. 导入一个文本文件sqlldr是否可以胜任？YES
3. 是否可以批量导入多个文本文件？YES

在满足客户需求的前提下，简单的就是最好的！