Oracle sqlldr

sqlldr是操作系统命令行下的命令，用来将文本格式数据(或csv)导入到oracle数据库。

命令：

sqlldr userid=user/password control=data.ctl log=0228.log parallel=yes direct=ye

控制文件：

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'
)

参数说明：

    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