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(苦练七十二变,笑对八十一难)