sqlldr--数据加载工具

1.sqlldr简介

      sqlldr即SQL*Loader，SQL*Loader是Oracle的高速批量的数据加载工具，通常用来将操作系统文件（数据）迁移到Oracle数据库中，是大型数据仓库选择使用的加载方法。

其可用于多种平面文件格式向Oralce数据库中加载数据，sqlldr可以在极短的时间内加载数量庞大的数据，其有两种操作模式：

1）传统路径（conventional path）：sqlldr会利用SQL插入为我们加载数据

2）直接路径(direct path)：采用这种模式，sqlldr不使用SQL，而是直接格式化数据库块

    利用直接路径加载，可以从一个平面文件读数据，并将其直接写至格式化的数据库块，而绕过整个SQL引擎和undo生成，同时还可能避开redo生成。若要在一个没有任何数据的数据库中充分加载数据，最快的方法就是采用并行直接路径加载。

    在使用sqlldr时，需要有一个控制文件（control file），控制文件中包含描述输入数据的信息（如输入数据的布局、数据类型等），另外还包含有关目标表的信息，控制文件甚至还可以包含要加载的数据。

2.sqlldr特点

基本特点

1）能装入不同数据类型文件及多个数据文件的数据

2）可装入固定格式，自由定界以及可度长格式的数据

3）可以装入二进制，压缩十进制数据

4）一次可对多个表装入数据

5）连接多个物理记录装到一个记录中

6）对一单记录分解再装入到表中

7）可以用 数对制定列生成唯一的KEY

8）可对磁盘或 磁带数据文件装入制表中

9）提供装入错误报告

10）可以将文件中的整型字符串，自动转成压缩十进制并装入列表中

3.sqllldr命令行参数

        sqlldr命令行参数可以直接放在命令行中，也可以写在控制文件中，还可以写在参数文件PARFILE（parameter file）里。

PS：在重复指定的参数里面，命令行的参数优先级大于写在参数文件与控制文件中的参数

[oracle@database01 ~]$ sqlldr

SQL*Loader: Release 11.2.0.4.0 - Production on Tue Jan 10 17:06:51 2023

Copyright (c) 1982, 2011, 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)

PLEASE NOTE: Command-line parameters may be specified either by

position or by keywords.  An example of the former case is 'sqlldr

scott/tiger foo'; an example of the latter is 'sqlldr control=foo

userid=scott/tiger'.  One may specify parameters by position before

but not after parameters specified by keywords.  For example,

'sqlldr scott/tiger control=foo logfile=log' is allowed, but

'sqlldr scott/tiger control=foo log' is not, even though the

position of the parameter 'log' is correct.

4.控制文件

###命令行

sqlldr userid=用户名/密码@实例  control=loid.ctl data=/export/home/oracle/liang/hx.txt bad=/export/home/oracle/liang/log/loid.bad

###控制文件

OPTIONS(direct=TRUE,parallel=TRUE,multithreading=TRUE,readsize=3072000,BINDSIZE=8388608,READSIZE=8388608,ERRORS=10000,ROWS=1000000)

load data

infile '/export/home/oracle/liang/hx.txt'

truncate into table tablequerymanager.tmp_optical_bpp_loid

fields terminated by ','

optionally enclosed by '"'

trailing nullcols

(      code_name

)

###OPTIONS说明

OPTIONS ([SKIP=integer]，[LOAD = integer]，[ERRORS = integer]，[ROWS=integer]，[BINDSIZE=integer]，[SILENT=(ALL|FEEDBACK|ERROR|DISCARD)]

###OPTIONS说明

skip=1                #表示跳过第一行，从第二行开始导入

load=200000            #表示并不导入所有的数据，只导入跳过skip参数后的200000条数据

rows=1000              #表示一次加载的行数，默认值为64，此处设置为1000

errors=100            #表示出错100次后，停止加载

bindsize=33554432      #表示每次提交记录缓冲区的大小，默认256k

###控制文件说明

load data

infile '/export/home/oracle/liang/hx.txt'                            #指定数据文件绝对路径               

truncate into table tablequerymanager.tmp_optical_bpp_loid          #指定导入库表

fields terminated by ','                                            #指定分隔符

optionally enclosed by '"'

                                          #指定界定符

trailing nullcols

(      code_name "replace(:username,chr(13),'')"

)