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本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
