ITPub博客

首页 > 数据库 > Oracle > SQL*Loader的使用总结(一)

SQL*Loader的使用总结(一)

原创 Oracle 作者:梓沐 时间:2016-02-15 10:28:25 0 删除 编辑
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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
擅长PLS/QL开发,SQL调优和改写,数据库设计

注册时间:2014-08-18

  • 博文量
    161
  • 访问量
    1086074