oracle sql 案例,Oracle SQL*Loader使用案例（一）

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

一、sqlldr的命令帮助信息

[oracle@wjq ~]$sqlldr

SQL*Loader: Release

11.2.0.4.0 - Production on Tue Oct 31 11:46:27 2017

Copyright (c) 1982, 2011,

Oracle and/or its affiliates.  All rights

reserved.

Usage: SQLLDR keyword=value

[,keyword=value,...]

Valid Keywords:

userid -- ORACLE username/password#Oracle用户名和口令

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)#要跳过的逻辑记录数目(默认0)

load -- number of logical records to load(Default

all)#要加载的逻辑记录数目(默认全部)

errors -- number of errors to allow

(Default 50)#允许错误的数目(默认50)

rows -- number of rows in conventional

path bind array or between direct path data saves

(Default: Conventional path 64,

Direct path all)#常规路径绑定数组中或直接路径保存数据间的行数(常规路径默认64,直接路径默认全部)

bindsize -- size of conventional path bind

array in bytes  (Default 256000)#常规路径绑定数据的大小(默认256000字节)

silent -- suppress messages during run

(header,feedback,errors,discards,partitions)#运行过程中隐藏的信息(标题，反馈，错误，废弃，分区)

direct -- use direct path                      (Default FALSE)#使用直接路径(默认FALSE)

parfile -- parameter file: name of file that

contains parameter specifications#参数文件，包括参数说明文件的名称

parallel -- do parallel load                     (Default FALSE)#执行并行加载(默认FALSE)

file -- file to allocate extents

from#要从以下文件中分配区的文件

skip_unusable_indexes --

disallow/allow unusable indexes or index partitions  (Default FALSE)#不允许/允许使用无用的索引(默认FALSE)

skip_index_maintenance -- do

not maintain indexes, mark affected indexes as unusable  (Default FALSE)#不维护索引，对受到影响的索引标记为失效(默认FALSE)

commit_discontinued -- commit

loaded rows when load is discontinued

(Default FALSE)#提交加载中断时已加载的行(默认FALSE)

readsize -- size of read buffer                  (Default 1048576)#读取缓冲区的大小(默认1048576字节)

external_table -- use

external table for load; NOT_USED, GENERATE_ONLY, EXECUTE  (Default NOT_USED)#使用外部表进行加载:NOT_USED, GENERATE_ONLY, EXECUTE(默认NOT_USED)

columnarrayrows -- number of

rows for direct path column array

(Default 5000)#直接路径列数组的行数(默认5000)

streamsize -- size of direct

path stream buffer in bytes  (Default

256000)#直接路径流缓冲区的大小(默认256000)

multithreading -- use

multithreading in direct path#在直接路径中使用多线程

resumable -- enable or disable resumable for

current session  (Default FALSE)#启用或禁用当前可恢复会话(默认FALSE)

resumable_name -- text string

to help identify resumable statement#有助于标识可恢复语句的文本字符串

resumable_timeout -- wait

time (in seconds) for RESUMABLE  (Default

7200)#RESUMABLE的等待时间(默认7200秒)

date_cache -- size (in

entries) of date conversion cache

(Default 1000)#日期转换高速缓冲区大小(以条目计)(默认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.

注意：

SQLLDR的参数组合比较灵活，即可以直接写值，也可以写关键字=值。例如：sqlldr scott/tiger foo和sqlldr control=foo userid=scott/tiger两种写法均有效。

二、使用案例

1.1简单例子

新建一个wjq_test1.ctl的控制文件，控制文件的名称和文件类型可以任意指定，接着在控制文件中写入内容

SCOTT@seiang11g>create table

tb_loader as select * from bonus;

Table created.

SCOTT@seiang11g>desc tb_loader

Name                             Null?    Type

-------------------------------- --------

------------------------------------

ENAME                                     VARCHAR2(10)

JOB                                       VARCHAR2(9)

SAL                                       NUMBER

COMM                                      NUMBER

控制文件内容如下：

[oracle@wjq SQL*Loader]$vim wjq_test1.ctlLOAD DATA

INFILE *

INTO TABLE tb_loader

FIELDS TERMINATED BY ","

(ENAME,JOB,SAL)

BEGINDATA

SMITH,CLEAK,3904

ALLEN,SALESMAN,2891

WARD,SALESMAN,3128

KING,PRESIDENT,2523

执行sqlldr命令

[oracle@wjq SQL*Loader]$sqlldr scott/tiger

control=/u01/app/oracle/SQL*Loader/wjq_test1.ctl

SQL*Loader: Release 11.2.0.4.0 - Production on Tue Oct 31 14:43:12 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 4

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

SCOTT@seiang11g>select * from tb_loader;

ENAME      JOB              SAL       COMM

---------- --------- ---------- ----------

SMITH      CLEAK           3904

ALLEN      SALESMAN        2891

WARD       SALESMAN        3128

KING       PRESIDENT       2523

发现查询到的内容就是控制文件中BEGINDATA中的数据，数据已经被成功载入。

提示：要插入的表必须在数据库中已经存在，再使用sqlldr向其中加载数据

2、SQL*Loader的体系分析LOAD DATA

------------------------------------

INFILE *

INTO TABLE tb_loader

FIELDS TERMINATED BY

","

(ENAME,JOB,SAL)

BEGINDATA

------------------------------------

SMITH,CLEAK,3904

ALLEN,SALESMAN,2891

WARD,SALESMAN,3128

KING,PRESIDENT,2523

1.2控制文件解析

①第一部分：

LOAD DATA是标准语法，控制文件一般都以此开头，LOAD DATA前还可指定UNRECOVERABLE或RECOVERABLE来控制此次加载的数据是否可恢复，或者指定CONTINUE_LOAD，表示继续加载，控制文件的其他语句可以查看官方文档。

②中间部分：*INFILE:表示数据文件位置，如果值为*，表示数据就在控制文件中，本例中没有单独的数据文件，对于大多数加载而言，都会将数据文件与控制文件分离。*INTO TABLE tbl_name: tbl_name即数据要加载到的目标表，该表在你执行sqlldr命令之前必须已经创建。*INTO前还有一些很有意思的参数需要说明：*INSERT:向表中插入数据，表必须为空，如果表非空的话，执行sqlldr命令时会报错，默认就是INSERT参数。*APPEND:向表中追加数据，不管表中是否有数据。*REPLACE:替换表中数据，相当于先DELETE表中全部数据,然后再INSERT。*TRUNCATE:类似REPLACE,只不过这里不是用DELETE方式删除表中数据，而是通过TRUNCATE的方式删除，然后再INSERT。*FIELDS TERMINATED BY "，"：设置数据部分字符串的分隔值，这里设置为逗

号(,)分隔，当然也可以换成其他任意可见字符，只要确定那是数据行中的分隔符就行。*(ENAME,JOB,SAL):要插入的表的列名，这里需要注意的是列名要与表中列名完全相同，列的顺序可以与表中列顺序不同，但是必须与数据部分的列一一对应?

*BEGINDATA:表示以下为待加载数据，仅当INFILE指定为*时有效。

③数据部分在该案例中，是将数据部分与控制部分都放在控制文件中，通常这部分是独立存在于一个文本文件中。如果是独立的数据文件，只需要将控制文件中INFILE参数后面的*改为数据文件的文件名即可。

1.3日志文件解析

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

[oracle@wjq SQL*Loader]$cat wjq_test1.log

SQL*Loader: Release 11.2.0.4.0 - Production on Tue Oct 31 14:43:12 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Control File:

/u01/app/oracle/SQL*Loader/wjq_test1.ctl

Data File:

/u01/app/oracle/SQL*Loader/wjq_test1.ctl

Bad File:     /u01/app/oracle/SQL*Loader/wjq_test1.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 TB_LOADER, 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 TB_LOADER:

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 Tue Oct 31 14:43:12 2017

Run ended on Tue Oct 31 14:43:12 2017

Elapsed time was:     00:00:00.03

CPU time was:         00:00:00.01

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

1.4错误文件解析

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

1.5废弃文件解析

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

关于更多SQL*Loader的使用案例作者的博客将会持续的进行更新，敬请期待......

作者：SEian.G(苦练七十二变，笑对八十一难)