Oracle sqlldr使用

Oracle SQLLDR用法


SQL*LOADER是ORACLE的数据加载工具，通常用来将操作系统文件迁移到ORACLE数据库中。SQL*LOADER是大型数据
仓库选择使用的加载方法，因为它提供了最快速的途径（DIRECT，PARALLEL）。

SQLLDR的有效关键字

有效的关键字:

    userid -- ORACLE 用户名/口令
   control -- 控制文件名
       log -- 日志文件名
       bad -- 错误文件名(文件中包含由于错误拒绝的数据记录,比如错误的数据类型或者完整性约束)
      data -- 数据文件名
   discard -- 废弃文件名 (文件中包含被控制文件中的过滤语句过滤掉的记录)
discardmax -- 允许废弃的文件的数目         (全部默认)
      skip -- 要跳过的逻辑记录的数目  (默认 0)
      load -- 要加载的逻辑记录的数目  (全部默认)
    errors -- 允许的错误的数目         (默认 50)
      rows -- 常规路径绑定数组中或直接路径保存数据间的行数 (默认: 常规路径 64, 所有行数直接路径)(这里的ROWS在直接路径中貌似只在信息输出中显示逻辑记录计数,但不提交貌似)          
  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)

--来看个实例,result.csv为数据文件

result.csv内容:

1,默认 Web 站点,192.168.2.254:80:,RUNNING
2,other,192.168.2.254:80:test.com,STOPPED
3,third,192.168.2.254:81:thirdabc.com,RUNNING

从中，我们看出4列，分别以逗号分隔，为变长字符串。

--控制文件内容,控制文件是包含对数据如何被加载的信息

创建制定控制文件result.ctl内容如下:

load data
infile 'result.csv' --可以写多个infile,支持一次性导入多个数据文件
into table resultxt
fields terminated by ','
TRAILING NULLCOLS--指当数据文件的最后一列为空时,Oracle会报错( ENTIRE_LINE 出现错误。在逻辑记录结束之前未找到列),使用TRAILING NULLCOLS可以解决这个问题
(
resultid,
website,
ipport,
status
)

说明：
infile　--指数据源文件
into table resultxt --默认是INSERT,APPEND 在表中追加新记录,REPLACE 删除旧记录,替换成新装载的记录 TRUNCATE 同上
terminated by ','　--指用逗号分隔
terminated by whitespace　--结尾以空白分隔

--创建目标表
create table resultxt
(
resultid varchar2(50),
website varchar2(50),
ipport varchar2(50),
status varchar2(50)
);

--开始执行,sqlldr会默认在D盘根目录(执行命令的目录)下寻找控制文件以及数据文件,除非指定目录
--若不通过log命令来指定日志文件,Oracle会自动生成(名称默认为控制文件的名称,.log作为扩展名)
D:\>sqlldr userid=admin/admin control=result.ctl log=resulthis.out

SQL*Loader: Release 10.2.0.1.0 - Production on 星期三 1月 8 14:07:14 2014

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

达到提交点 - 逻辑记录计数 2
达到提交点 - 逻辑记录计数 3


--以下是sqlldr的日志文件内容

SQL*Loader: Release 10.2.0.1.0 - Production on 星期三 1月 8 14:07:14 2014

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

控制文件:      result.ctl
数据文件:      result.csv
错误文件:      result.bad
废弃文件:      未作指定
 
(可废弃所有记录)

要加载的数: ALL
要跳过的数: 0
允许的错误: 50
绑定数组: 64 行, 最大 256000 字节
继续:    未作指定
所用路径: 常规

表 RESULTXT,已加载从每个逻辑记录
插入选项对此表 INSERT 生效

   列名                        位置      长度  中止 包装数据类型
------------------------------ ---------- ----- ---- ---- ---------------------
RESULTID                            FIRST     *   ,       CHARACTER            
WEBSITE                              NEXT     *   ,       CHARACTER            
IPPORT                               NEXT     *   ,       CHARACTER            
STATUS                               NEXT     *  WHT      CHARACTER            


表 RESULTXT:
3 行 加载成功。
由于数据错误, 0 行 没有加载。
由于所有 WHEN 子句失败, 0 行 没有加载。
由于所有字段都为空的, 0 行 没有加载。


为绑定数组分配的空间: 66048 字节 (64 行)
读取   缓冲区字节数: 1048576

跳过的逻辑记录总数: 0
读取的逻辑记录总数: 3
拒绝的逻辑记录总数: 0
废弃的逻辑记录总数: 0

从 星期三 1月  08 14:07:14 2014 开始运行
在 星期三 1月  08 14:07:15 2014 处运行结束

经过时间为: 00: 00: 00.89
CPU 时间为: 00: 00: 00.06

--下面是按照固定长度进行分割的控制文件内容
--
OPTIONS (BINDSIZE=65536000,DIRECT=TRUE,ERRORS=100000000,DISCARDMAX =0,SKIP=7,ROWS=100000) 
LOAD DATA
INFILE 'F:\SM-HQ-20131227\SM-HQ-20131227\84744.DATA'
APPEND INTO TABLE TMP2
TRAILING NULLCOLS
(
BRCH POSITION(3:8), 
JOURNAL_NBR POSITION(10:21),
JOURNAL_ID POSITION(23:33),
POST_DATE POSITION(35:45),
TRAN_DATE POSITION(47:57),
GL_CLASS_CODE POSITION(71:74),
TRAN_CODE POSITION(86:94),
TRAN_NAME POSITION(95:124),
FCY_CODE POSITION (125:127),
AMT POSITION(128:178) "REPLACE(:AMT, ',', '')",
ACCOUNT_NO POSITION(179:196),
SOURCE_APPLN POSITION(197:208),
ERROR_NO POSITION(211:220),
TELLER_NO POSITION(221:229),
CHANNEL_TYPE POSITION(232:243),
PROM_CODE POSITION(246:250)
)

--测试下直接加载与传统加载对表的高水位的影响

CREATE TABLE T AS SELECT * FROM DBA_OBJECTS;
DELETE T;

admin@ORCL> set serveroutput on
admin@ORCL> exec show_space ('T');
Unformatted Blocks .....................               0
FS1 Blocks (0-25) ......................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................           1,014
Full Blocks ............................               0
Total Blocks............................           1,152
Total Bytes.............................       9,437,184
Total MBytes............................               9
Unused Blocks...........................             112
Unused Bytes............................         917,504
Last Used Ext FileId....................               9
Last Used Ext BlockId...................         510,985
Last Used Block.........................              16

--先测试直接加载

options (direct=true)
load data
infile 'T.csv' 
APPEND into table T 
fields TERMINATED BY ',' optionally enclosed by '"'
(
OWNER,
OBJECT_NAME,
SUBOBJECT_NAME,
OBJECT_ID,
DATA_OBJECT_ID,
OBJECT_TYPE,
CREATED "to_date(:CREATED,'YYYY/MM/DD HH24:MI:SS')", 
LAST_DDL_TIME  "to_date(:LAST_DDL_TIME,'YYYY/MM/DD HH24:MI:SS')",
TIMESTAMP,
STATUS,
TEMPORARY,
GENERATED,
SECONDARY 
)

--执行sqlldr
D:\>sqlldr admin/admin control=result.ctl log=result.log

加载完成 - 逻辑记录计数 70575。

--查看段T块的使用量,可以看出块几乎增加了一倍
admin@ORCL> set serveroutput on
admin@ORCL> exec show_space ('T');
Unformatted Blocks .....................               0
FS1 Blocks (0-25) ......................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................           1,014
Full Blocks ............................           1,013
Total Blocks............................           2,176
Total Bytes.............................      17,825,792
Total MBytes............................              17
Unused Blocks...........................             107
Unused Bytes............................         876,544
Last Used Ext FileId....................               9
Last Used Ext BlockId...................         512,009
Last Used Block.........................              21

PL/SQL 过程已成功完成。

--重复上面的步骤，利用常规插入(direct=false)，看看块是否增加
--结果是段T的总数据块没有增加,Oracle会自动在段下寻找空闲块进行插入

admin@ORCL> exec show_space ('T');
Unformatted Blocks .....................               0
FS1 Blocks (0-25) ......................               0
FS2 Blocks (25-50) .....................               1
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               1
Full Blocks ............................           1,012
Total Blocks............................           1,152
Total Bytes.............................       9,437,184
Total MBytes............................               9
Unused Blocks...........................             112
Unused Bytes............................         917,504
Last Used Ext FileId....................               9
Last Used Ext BlockId...................         510,985
Last Used Block.........................              16

PL/SQL 过程已成功完成。

--直接路径加载将使唯一索引以及主键索引无效,这点需要注意


admin@ORCL> create table t as select * from dba_objects;

表已创建。

admin@ORCL> alter table t add primary key(object_id);

表已更改。

admin@ORCL>
admin@ORCL> create index idx_t_objectid on t(data_object_id);

索引已创建。

admin@ORCL> select index_name,status from dba_indexes where table_name = 'T' and owner = 'ADMIN';

INDEX_NAME                     STATUS
------------------------------ --------
SYS_C0012772                   VALID
IDX_T_OBJECTID                 VALID

--插入部分数据
D:\sqlldr>sqlldr admin/admin control=LDR_T.ctl


--索引变成无效了,这里是因为输入数据中包含导致索引无效的数据，而Oracle并不报错，继续执行，导致主键无效。
--若输入的数据不会导致重复数据，则索引不会无效
admin@ORCL> select index_name,status from dba_indexes where table_name = 'T' and owner = 'ADMIN';

INDEX_NAME                     STATUS
------------------------------ --------
SYS_C0012773                   UNUSABLE
IDX_T_OBJECTID                 VALID

--在输出的log日志中，也证明了以上观点
表 T 的以下索引已处理:
索引 ADMIN.IDX_T_OBJECTID 已成功加载, 具有 24006 个关键字
索引 ADMIN.SYS_C0012773 无法使用, 因为: 
ORA-00001: 违反唯一约束条件 (ADMIN.SYS_C0012775)


--关注下性能相关的参数
--从传统路径摘取的部分内容,rows采取默认值

绑定数组: 64 行, 最大 256000 字节
为绑定数组分配的空间:                214656 字节 (64 行)
读取   缓冲区字节数: 1048576


--将rows提高到1000,可以看出虽然rows设置了为1000行一提交，但实际上Oracle调整为76行，因为绑定数组的大小限制在25600个字节,
--在传统加载的情况，rows的大小受与绑定数组分区空间大小影响的
绑定数组: 1000 行, 最大 256000 字节
为绑定数组分配的空间:                254904 字节 (76 行)
读取   缓冲区字节数: 1048576


--将bindsize设置为10000000时，Oracle提高以下内容，间接说明了readsize不能小于bindszie,oracle会自动调整readsize
--影响传统路径加载的参数有
--bindsize(绑定数据的大小)
--readsize(读取缓冲区)
readsize 的指定值 (1048576) 小于 bindsize(10000000)

绑定数组: 1000 行, 最大 10000000 字节
为绑定数组分配的空间:  354000 字节 (1000 行)
读取   缓冲区字节数:  10000000



--来看来direct=true时,相关的参数影响,设置rows=1000
--根据日志内容可以得出结论,当采用直接路径时,bindsize是不起作用的，它只会影响传统路径的加载
--而影响直接路径加载的参数包括
--columnarrayrows(列数组)
--streamsize(流缓冲区)
--readsize(读取缓冲区)


在直接路径中没有使用绑定数组大小。
列数组  行数:    1000
流缓冲区字节数:  256000
读取   缓冲区字节数: 1048576

--以上我们知道了影响sqlldr的相关参数，但设置多大比较合适是需要进一步探究的。

另外的关于SQLLDR提高的速度的文章：


http://www.dba-oracle.com/tips_sqlldr_loader.htm
https://cloud.tencent.com/developer/article/1993111