SQL*Loader的使用总结(一)

SQL*Loader是oracle提供的可以从多种平面文件中向数据库中加载数据的工具，使用sqlldr工 具可以在很短的时间内向数据库中加载大量的数据，像把制作好的excel表格导入数据库，可以说非常方便，相关的数据加载和卸载工具还有外部 表，IMP/EXP，数据泵等，其实呢~ 关于SQL*Loader的学习多数时间是花在了琢磨sqlldr控制文件的写法上，下面来总结我的SQL*Loader学习过程和一些实验案例。

一、sqlldr的命令帮助信息
[oracle@cancer ~]$ sqlldr
SQL*Loader: Release 11.2.0.4.0 - Production on Tue Jan 26 16:54:30 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Usage: SQLLDR keyword=value [,keyword=value,...]
Valid Keywords:
--------------------------------------------------------------------------------------------------
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)
注意：SQLLDR的参数组合比较灵活，即可以直接写值，也可以写关键字=值。
例如：sqlldr scott/tiger foo 和 sqlldr control=foo userid=scott/tiger 两种写法均有效。
--------------------------------------------------------------------------------------------------

二、操作案例
1.简单例子
新建一个ldr_case1.ctl的控制文件，控制文件的名称和文件类型可以任意指定，接着在控制文件中写入内容

1. LOAD DATA
   INFILE *
   INTO TABLE BONUS
   FIELDS TERMINATED BY ","
   (ENAME,JOB,SAL)
   BEGINDATA
   SMITH,CLEAK,3904
   ALLEN,SALESMAN,2891
   WARD,SALESMAN,3128
   KING,PRESIDENT,2523
   

执行sqlldr命令

1. [oracle@cancer ~]$ sqlldr scott/tiger control=ldr_case1.ctl
   
   SQL*Loader: Release 11.2.0.4.0 - Production on Wed Jan 27 08:23:36 2016
   
   Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
   
   Commit point reached - logical record count 4
   

可以发现提示已经生成了4条数据，接着连接数据库查看一下内容

1. [oracle@cancer ~]$ sqlplus scott/tiger;
   
   SQL> select * from bonus;
   
   ENAME   JOB       SAL        COMM
   ------- --------- ---------- ----------
   SMITH   CLEAK     3904
   ALLEN   SALESMAN  2891
   WARD    SALESMAN  3128
   KING    PRESIDENT 2523
   

发现查询到的内容就是控制文件中BEGINDATA中的数据，数据已经被成功载入。
提示：要插入的表必须在数据库中已经存在，再使用sqlldr向其中加载数据

2.SQL*Loader的体系分析

1. LOAD DATA
   -------------------------
   INFILE *
   INTO TABLE BONUS
   FIELDS TERMINATED BY ","
   (ENAME,JOB,SAL)
   BEGINDATA
   -------------------------
   SMITH,CLEAK,3904
   ALLEN,SALESMAN,2891
   WARD,SALESMAN,3128
   KING,PRESIDENT,2523
   

2.1控制文件解析
①第一部分：
LOAD DATA是标准语法，控制文件一般都以此开头，LOAD DATA前还可指定 UNRECOVERABLE或RECOVERABLE来控制此次加载的数据是否可恢复，或者指定CONTINUE_LOAD，表示继续加载，控制文件的其他语句可以查看官方文档。
②中间部分：
*INFILE:表示数据文件位置，如果值为*，表示数据就在控制文件中，本例中没有单独的数据文件，对于大多数加载而言，都会将数据文件与控制文件分离。
*INTO TABLE tbl_name: tbl_name即数据要加载到的目标表，该表在你执行sqlldr命令之前必须已经创建。
*INTO前还有一些很有意思的参数需要说明：
  a)INSERT:向表中插入数据，表必须为空，如果表非空的话，执行sqlldr命令时会报错，默认就是INSERT参数。
  b)APPEND:向表中追加数据，不管表中是否有数据。
  c)REPLACE:替换表中数据，相当于先DELETE表中全部数据,然后再INSERT。
  d)TRUNCATE:类似REPLACE,只不过这里不是用DELETE方式删除表中数据，而是通过TRUNCATE的方式删除，然后再INSERT。
*FIELDS TERMINATED BY "，"：设置数据部分字符串的分隔值，这里设置为逗 号（,）分隔，当然也可以换成其他任意可见字符，只要确定那是数据行中的分隔符就行。
*(ENAME,JOB,SAL):要插入的表的列名，这里需要注意的是列名要与表中列名完全相同，列的顺序可以与表中列顺序不同，但是必须与数据部分的列一一对应?
*BEGINDATA:表示以下为待加载数据，仅当INFILE指定为*时有效。
③数据部分
在该案例中，是将数据部分与控制部分都放在控制文件中，通常这部分是独立存在于一个文本文件中。如果是独立的数据文件，只需要将控制文件中INFILE参数后面的*改为数据文件的文件名即可。

2.2日志文件解析
在默认情况下，sqlldr命令在执行过程中，会自动产生一个与控制文件同名的日志文件，文件扩展名为.log,日志文件中记录了加载过程中的各项统计信息，如一些初始化参数、读取的记录数、成功加载的记录数、加载用时等。
前例中，执行完sqlldr命令之后，相同路径下应该生成了一个ldr_case1.log文件，直接以“记事本”工具打开查看，应该显示如下内容：

1. [oracle@cancer ~]$ cat ldr_case1.log
   
   SQL*Loader: Release 11.2.0.4.0 - Production on Wed Jan 27 08:23:36 2016
   
   Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
   
   Control File:   ldr_case1.ctl
   Data File:      ldr_case1.ctl
    Bad File:     ldr_case1.bad
    Discard File:  none specified
   
   (Allow all discards)
   
   Number to load: ALL
   Number to skip: 0
   Errors allowed: 50
   Bind array:     64 rows, maximum of 256000 bytes
   Continuation:    none specified
   Path used:      Conventional
   
   Table BONUS, loaded from every logical record.
   Insert option in effect for this table: INSERT
   
     Column Name                  Position   Len  Term Encl Datatype
   ------------------------------ ---------- ----- ---- ---- ---------------------
   ENAME                               FIRST     *   ,       CHARACTER            
   JOB                                  NEXT     *   ,       CHARACTER            
   SAL                                  NEXT     *   ,       CHARACTER            
   
   
   Table BONUS:
    4 Rows successfully loaded.
    0 Rows not loaded due to data errors.
    0 Rows not loaded because all WHEN clauses were failed.
    0 Rows not loaded because all fields were null.
   
   
   Space allocated for bind array:                  49536 bytes(64 rows)
   Read   buffer bytes: 1048576
   
   Total logical records skipped:          0
   Total logical records read:             4
   Total logical records rejected:         0
   Total logical records discarded:        0
   
   Run began on Wed Jan 27 08:23:36 2016
   Run ended on Wed Jan 27 08:23:36 2016
   
   Elapsed time was:     00:00:00.39
   CPU time was:         00:00:00.01
   

日志文件结构简单，前面都是初始化的参数，中间及后半部分才是我们应该关注的，包括记录的结构、操作的记录数（含成功的和错误的）、花费的时间等，如在这个日志文件中加粗的部分显示己经成功载入了 4条，共费时近40毫秒。

2.3错误文件解析
sqlldr命 令在执行过程中，不仅会产生日志文件，如果加载数据过程中由于数据不符合规范导致加载错误，还会产生一个同名的错误文件，文件扩展名为bad(如果DBA 不 显式指定的话）。该文件中记录了出错的数据。错误文件中数据的格式与数据文件完全相同，因此如果发现加载时出现错误文件，根据日志文件分析出错原因，解决 后修改控制文件中infile参数为错误文件，然后重新执行sqlldr命令即可。

2.4废弃文件解析
除了日志文件和错误文件，执行sqlldr命令时还有可能生成一个同名的废弃文件,文件扩展名为.dsc，在默认情况下不会有，必须在执行sqlldr命令时显式指定废弃文件，并确实存在不符合导入逻辑的记录，里面记录了未被插入的数据


本文内容参考<涂抹Oracle-三思笔记>一书，该书是基于Windows，本文引用了该书的脚本和结论的整理在Linux亲自测试通过，并对一些小问题进行了处理

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29812844/viewspace-1988868/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29812844/viewspace-1988868/