Oracle sqlldr

来源：互联网发布：淘宝上比较好的店铺编辑：程序博客网时间：2024/04/30 02:23

分类：

Oracle（22）

sqlldr是操作系统命令行下的命令，用来将文本格式数据导入到Oracle数据库中。

------------------------------------------------------------------简单示例--------------------------------------------------------------------

命令：

[plain] view plaincopy

[oracle@localhost dataBak]$ sqlldr username/password@ORACLE_SID control=xxx.ctl log=xxx.log bad=xxx.bad

控制文件：

[plain] view plaincopy

load data --控制文件标识
CHARACTERSET AL32UTF8 --文件字符集编码,如果跟数据库不同,会自动转换,比如文件为UTF-8,数据库GBK,这里就写AL32UTF8,导入时会自动转换成ZHS16GBK
infile "/data/test.dat" --需要导入的外部数据文件
infile "/data/test1.dat" --可指定多个数据文件(多文件装载)
append into table TableName --向表中追加数据，还可以其他选项，下面详解
fields terminated by "," --外部文件的数据以“,”分隔
--fields terminated by X'09' --外部文件的数据以X'09'分割，制表符（TAB）,ASCII码chr(9)
Optionally enclosed by '"' --源数据中每个字段如果用 "" 框起，而导入时不需要
trailing nullcols --表中的字段没有对应的值时填充空值
( --定义列对应顺序,
id integer external, --integer external字符格式整数
name "upper(:name)", --将插入的值转换为大写
con ":id||:name", --表中CON列的值是ID和NAME的组合值
dt date"yyyy-mm-dd hh24:mi:ss", --插入日期型数据
ch char(500), --插入大于256长度的需要添加char函数，不然报错超出长度。
PPN_TMSTAMP "to_date(substr(:PPN_TMSTAMP,0,INSTR(:PPN_TMSTAMP, '.', 1, 1)-4),'yyyy-mm-dd')" --'2013-04-04-00.22.00.000000'
)

------------------------------------------------------------------shell示例--------------------------------------------------------------------

导入pisa_tableList.txt文件里的数据库表名列表的所有表数据。

百度云下载脚本：http://pan.baidu.com/s/1mgxUvwO

[plain] view plaincopy

#!/bin/bash
:<<COMMENTBLOCK
----------------sqlldr导入Oracle数据------------
----------------author LiuJiaHua----------------
----------------Email 6769884@qq.com------------
----------------date 2015-06-19-----------------
COMMENTBLOCK
source ~/.bash_profile
etlPath=/s01/etl_data_pisa
originaldataFile=${etlPath}/originaldata
data_dir=${etlPath}/data
bin_dir=${etlPath}/bin
log_dir=${etlPath}/log
bad_dir=${etlPath}/bad
ctlFile=${etlPath}/ctl
#DataDate=$(date +%Y%m%d --date="-1 day")
DataDate="20150608"
markingFile1=${data_dir}/file_pisa_fdm_oracleData_${DataDate}.ok
echo =============$markingFile1
while [ 1 -eq 1 ]
do
if [ -f "$markingFile1" ]
then
echo "================所有Oracle需求数据已准备完成，开始导入数据到Oracle......================"
#修改ctl文件中数据文件日期后缀，将“@”换为当前数据日期
sed -i "s/@/${DataDate}/g" `grep @ -rl /s01/etl_data_pisa/ctl`
#set -e
#set -o pipefail
echo "======================================================================================="
echo "========================================清除数据======================================="
echo "======================================================================================="
while read tableName
do
echo "========================删除表：${tableName}========================"
sqlplus -S /nolog<<!
conn jgbs/jgbs@jgbs
TRUNCATE TABLE ${tableName};
exit
!
done < ${bin_dir}/pisa_tableList.txt
echo "======================================================================================="
echo "========================================导入数据======================================="
echo "======================================================================================="
while read tableName
do
sqlldr jgbs/jgbs@jgbs control=${ctlFile}/$tableName.ctl bad=$bad_dir/$tableName.bad log=$log_dir/$tableName.log direct=true COLUMNARRAYROWS=50000 rows=50000
case $? in
"0") echo "========================表：${tableName}，数据在制定控制范围内成功装载========================";;
"1") echo "========================表：${tableName}，语法错误，导致sqlldr无法装载========================注意！！========================"
exit ;;
"2") echo "========================表：${tableName}，数据装载存在警告========================注意！！========================"
exit ;;
"3") echo "========================表：${tableName}，在装载过程中遭遇到致命错误========================注意！！========================"
exit ;;
esac
done < ${bin_dir}/pisa_tableList.txt
sed -i "s/${DataDate}/@/g" `grep ${DataDate} -rl /s01/etl_data_pisa/ctl`
echo "================数据已于"----------------`date`----------------"成功导入!================"
touch /s01/etl_data_pisa/data/file_pisa_mdm_oracleData_${DataDate}.ok
break
else
echo "================所有Oracle需求数据未完成导出，等待5分钟......================"
sleep 300
fi
done
exit

------------------------------------------------------------------详细说明--------------------------------------------------------------------

================命令参数：================

[plain] view plaincopy

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 – 装载使用到的帐号信息，包括数据库信息，格式如：userid=user/passwd@dbname
control – 指定装载使用到的控制文件
log - 指定装载使用到的日志文件
bad -指定装载使用到的BAD文件
data -指定装载使用到的数据文件,如果在控制文件中也制定infile文件，那么将优先使用命令行的data文件，control文件中的第一个infile文件将忽略，如果control文件中只有一个data文件那么sqlldr将给出一个警告。
discard – discard 文件，不符合转载条件的记录文件
discardmax – 允许discards 数目，默认是全部
skip – 跳过记录数
load – 指定装载数据的逻辑数目，默认是说有记录。
注：逻辑记录和物理记录的区别：逻辑记录是指导入数据库的记录，物理记录数指文件中存在的记录。一般情况下逻辑记录跟物理记录相等，但是有些情况下，如，多条物理记录联合装载，这样可能出现不相等的情况。
errors – 允许出错数目，如果达到出错数就退出装载，默认50
rows – 装载提交数目，普通装载默认64，在直接路径如果没有指定默认为全部记录，通过这个特性可以用来控制sqlldr的事务一致性。
bindsize – 指定普通转载绑定数组的值，默认256000，该值之适用于普通装载，而不适用于直接路径装载，因为直接路径装载使用的是直接路径API，而不是普通装载的insert。设置大的bindsize可以提供每次insert的记录数目。
silent – 提示在装载时候终端上显示的内容，有以下几种可以选择。
Header：禁止sqlldr装载标题提示信息，但在log文件依然出现。
Feedback：禁止"commit point reached"提示信息。
Errors：禁止数据错误信息写在log日志文件，但是rejected records依然会写入。
Discards：禁止在log日志里面的记录信息写入discard文件。
Partitions：在采用直接路径装载的时候，取消写每个分区统计信息入日志文件。
ALL：包含以上所有的值
direct – 使用直接路径装载
parfile – 参数文件指定。
parallel – 并行装载，默认为FALSE，只能用于direct装载方式。
file – 指定装载过程中使用的数据库中的数据文件，该选项只适用于并行装载，通过该选项可以提高磁盘的读写速度。不同的装载可以存放不同的数据库文件。
skip_unusable_indexes –该值跟数据库配置文件中的skip_unusable_indexes类似，在装载过程中遇到无效索引的时候，不停下来继续进行装载。
skip_index_maintenance –停止索引的维护，在直接路径转载的时候。使用该选项将插入一个没用的索引键值代替把索引制成无效。这样不会因为前面的转载使索引无效而影响后面的装载。默认为FALSE。
commit_discontinued – 提交已经装载的行数，如果装载失败的时候。默认为FALSE。
readsize – 该参数用于设置读取数据的大小，如果读控制文件中的值就使用64K大小不变的值，readsize默认值为1048576，最大可设置的值为20M，在普通装载的情况下，bindsize的大小受限制与readsise的设置，如果readsize的值小于bindsize的值，sqlldr将会自动让readsize等于bindsize的值。
external_table –使用sqlldr来使用外部表的功能; 本选项提供三个可选项：
NOT_USED：默认为不使用外部表装载功能
GENERATE_ONLY：通过该选项可以生成需要通过外部表装载的所有步骤
EXECUTE：直接使用外部表功能装载
具体说明参见外部表装载
columnarrayrows –该选项适用于direct路径装载,默认为5000。该选项sqlldr将不做技术，如果使用该选项则需要用户设置正确，或者接受默认的值。
streamsize –该选项为直接路径装载所用，需要跟columnarrayrows配对使用。
multithreading - 在多cpu的系统中默认为TRUE，在单cpu的系统中默认为FALSE。使用多线程装载在direct path的情况下。推荐使用FALSE，因为在对于大数据量的转载中，可能出现中途挂起的情况。
resumable -设定可恢复的空间分配，默认为FALSE。通过该选项可以用于转载过程中空间不足引起的错误。
resumable_name –该值定义空间使用的语句。默认'User USERNAME (USERID), Session SESSIONID, Instance INSTANCEID'
resumable_timeout – 空间处理时间，默认7200秒。如果在这个时间内没有处理好空间，那么sqlldr将因为空间不足，而错误退出。该选项需要首先设resumable为TRUE。
date_cache –用于数据转化的临时空间默认是1000。本选项只能用于direct路径装载。在需要数据转化，如date，timestamp类新装载上可以使用，当然如果转换的数据唯一性很高，可能就很难有作用，对于很多重复的需要转换的比较有用，可以大大提高转化的时间。

sqlldr的命令行参数可以直接放在命令行，也可以放在控制文件，也可以直接放在参数文件PARFILE (parameter file)里面。
注意：在重复指定的参数里面，命令行的参数凌驾于任何写在参数文件和控制参数文件之上的参数。

================控制文件参数：================

OPTIONS （ { [SKIP=integer] [ LOAD = integer ]
[ERRORS = integer] [ROWS=integer]
[BINDSIZE=integer] [SILENT=(ALL|FEEDBACK|ERROR|DISCARD) ] )
LOAD[DATA]
[ { INFILE | INDDN } {file | * }
[STREAM | RECORD | FIXED length [BLOCKSIZE size]|
VARIABLE [length] ]
[ { BADFILE | BADDN } file ]
{DISCARDS | DISCARDMAX} integr ]
[ {INDDN | INFILE} . . . ]
[ APPEND | REPLACE | INSERT ]
[RECLENT integer]
[ { CONCATENATE integer |
CONTINUEIF { [THIS | NEXT] (start[: end])LAST }
Operator { 'string' | X 'hex' } } ]
INTO TABLE [user.]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]
}
[ ,...]
)
[INTO TABLE...]
[BEGINDATA]

一，要加载的数据文件：
1．INFILE 和INDDN是同义词，它们后面都是要加载的数据文件。如果用 * 则表示数据就在控制文件内。在INFILE 后可以跟几个文件，详细可以参考多文件装载的例子。
2．STRAM 表示一次读一个字节的数据。
3．RECORD 使用宿主操作系统文件及记录管理系统。
3．FIXED length 要读的记录长度为length字节，
4．VARIABLE 被读的记录中前两个字节包含的长度，length 记录可能的长度。默认为8k 字节。
5．BADFILE和BADDN同义。Oracle 不能加载数据到数据库的那些记录。
6．DISCARDFILE和DISCARDDN是同义词。记录没有通过的数据。
7．DISCARDS和DISCARDMAX是同义词。Integer 为最大放弃的文件个数。
二，加载方法
1．APPEND 给表添加行。
2．INSERT 给空表增加行（如果表中有记录则退出）。
3．REPLACE（truncate）先清空表在加载数据。
4．RECLEN 用于两种情况:
1）SQLLDR不能自动计算记录长度，
2）用户想看坏文件的完整记录时。
对于后一种，Oracle只能按常规把坏记录部分写到错误的地方。如果看整条记录，则可以将整条记录写到坏文件中。
三，指定最大的记录长度
CONCATENATE 允许用户设定一个整数，表示要组合逻辑记录的数目
四，建立逻辑记录：
1．THIS 检查当前记录条件，如果为真则连接下一个记录。
2．NEXT 检查下一个记录条件。如果为真，则连接下一个记录到当前记录来。
3．Start: end 表示要检查在THIS或NEXT字串是否存在继续串的列，以确定是否进行连接。如：continueif next(1-3)='WAG' 或continueif next(1-3)=X'0d03if'
五，指定要加载的表：
1．INTO TABLE 要加的表名。
2．WHEN 和select WHERE类似。用来检查记录的情况，如：when(3-5)='SSM' and (22)='*"
六，介绍并括起记录中的字段
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 ，则它们的顺序决定计算的顺序。
七，定义列：
column 是表列名。列的取值可以是：
BECHUM 表示逻辑记录数。第一个记录为1,第2个记录为2。
CONSTANT 表示赋予常数。
SEQUENCE 表示序列可以从任意序号开始，格式为：
SEQUENCE （ { integer | MAX |COUNT} [,increment]
POSITION 给出列在逻辑记录中的位置。可以是绝对的，或相对前一列的值。格式为：
POSITION ( {start[end] | * [+integer] } )
Start 开始位置
* 表示前字段之后立刻开始。
+ 从前列开始向后条的位置数。
八，定义数据类型：
1．字符类型数据
CHAR [ (length)] [delimiter]
length缺省为 1.
2.日期类型数据
DATE [ ( length)]['date_format' [delimiter]
使用to_date函数来限制。
3．字符格式中的十进制
DECIMAL EXTERNAL [(length)] [delimiter]
用于常规格式的十进制数（不是二进制=> 一个位等于一个bit）。
4．压缩十进制格式数据
DECIMAL （digtial [,precision]）
5．双精度符点二进制
DOUBLE
6．普通符点二进制
FLOAT
7．字符格式符点数
FLOAT EXTERNAL [ (length) ] [delimiter]
8．双字节字符串数据
GRAPHIC [ (legth)]
9．双字节字符串数据
GRAPHIC EXTERNAL[ (legth)]
10．常规全字二进制整数
INTEGER
11．字符格式整数
INTEGER EXTERNAL
12．常规全字二进制数据
SMALLINT
13．可变长度字符串
VARCHAR
14．可变双字节字符串数据
VARGRAPHIC

0 0