Sqlldr把文本文件导入到ORACLE中
1 查看命令参数
2 查看需要导入的文本内容
2.1 查看文本的行数
2.2 查看文本的大小
2.3 查看文本的内容
3 编写导数据的CTL文件
3.1 查看CTL文件
3.2 查看SHELL脚本
4 查看执行的效率
4.1 查看导入到ORACLE数据的效率
4.2 查看统计的日志
5 效率总结
1 查看命令参数
$ sqlldr --help
有效的关键字:
userid -- ORACLE 用户名/口令
control -- 控制文件名
log -- 日志文件名
bad -- 错误文件名
data -- 数据文件名
discard -- 废弃文件名
discardmax -- 允许废弃的文件的数目 (全部默认)
skip -- 要跳过的逻辑记录的数目 (默认 0)
load -- 要加载的逻辑记录的数目 (全部默认)
errors -- 允许的错误的数目 (默认 50)
rows -- 常规路径绑定数组中或直接路径保存数据间的行数
(默认: 常规路径 64, 所有直接路径)
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)
no_index_errors -- 出现任何索引错误时中止加载 (默认 FALSE)
PLEASE NOTE: 命令行参数可以由位置或关键字指定
。前者的例子是 'sqlldr
scott/tiger foo'; 后一种情况的一个示例是 'sqlldr control=foo
userid=scott/tiger'。位置指定参数的时间必须早于
但不可迟于由关键字指定的参数。例如,
允许'sqlldr scott/tiger control=foo logfile=log', 但是
不允许'sqlldr scott/tiger control=foo log', 即使
参数'log' 的位置正确
2 查看需要导入的文本内容
2.1 查看文本的行数
$ wc -l test-sqlldr.csv
9415010 test-sqlldr.csv
2.2 查看文本的大小
$ du -sh test-sqlldr.csv
2.8G test-sqlldr.csv
2.3 查看文本的内容
$ head -n 2 test-sqlldr.csv
K1|!?|K2|!?|GSMC|!?|JGLX|!?|KGS|!?|RZS|!?|ISJW|!?|SYR|!?|SYRLX|!?|CGZB|!?|TZLX|!?|KGLJS|!?|RZLX|!?|BSM
|!?||!?|北京罗马科技有限公司|!?|有限责任公司(台港澳法人独资)|!?|1|!?|3|!?|是|!?|马闯|!?|高级管理人员|!?||!?||!?||!?|总经理,执行董事,法定代表人/负责人/执行事务合伙人|!?|V1!ihMJSIrj/CeN5d2an64ys/iGqJnBLgEGHM9HVY1Xt91K5Pia73LMxGYHJewNqFBff6slsPFRHkRt<n>x0pV/J58JQ==
3 编写导数据的CTL文件
3.1 查看CTL文件
$ cat load-to-oracle.ctl
load data
CHARACTERSET UTF8
infile "/dazhihui/databack/b.csv"
append
into table xiaoxu.test_sqlldr
fields terminated by '|!?|'
trailing nullcols
(
K1,
K2,
GSMC,
JGLX,
KGS,
RZS,
ISJW,
SYR,
SYRLX,
CGZB,
TZLX,
KGLJS,
RZLX,
BSM
)
infile 后面是文件的路径
xiaoxu.test_sqlldr 需要的导入的表明
fields terminated by 后面跟着分隔符
trailing nullcols 字段的列表，需要全部大写与ORACLE保持一致，如果oracle字段小写需要改成小写
append 是追加的形式，也可以是insert (为缺省方式，在数据装载开始时要求表为空),append(在表中追加新记录) ,
replace (删除旧记录(用 delete from table 语句)，替换成新装载的记录),truncate(删除旧记录(用 truncate table 语句)，替换成新装载的记录)
3.2 查看SHELL脚本
$ cat import-data.sh
#!bin/bash
sqlldr username/password@IP:1521/etlpdb control=load.ctl log=test-log.log bad=bad.log errors=5000 rows=1000 bindsize=10485760
4 查看执行的效率
4.1 查看导入到ORACLE数据的效率
$ sh import-data.sh
************
Commit point reached - logical record count 9414355
Commit point reached - logical record count 9415010
Table XIAOXU.TEST_SQLLDR:
9415010 Rows successfully loaded.
Check the log file:
test-log.log
for more information about the load.
real 10m15.357s
user 1m27.074s
sys 0m2.278s
在以上可以看出导入进9415010行数据，用时 10m15.357s
4.2 查看统计的日志
$ cat test-log.log
SQL*Loader: Release 12.2.0.1.0 - Production on Fri Nov 9 17:46:51 2018
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Control File: load.ctl
Character Set UTF8 specified for all input.
Data File: /dazhihui/databack/b.csv
Bad File: bad.log
Discard File: none specified
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 5000
Bind array: 1000 rows, maximum of 10485760 bytes
Continuation: none specified
Path used: Conventional
Table XIAOXU.TEST_SQLLDR, loaded from every logical record.
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
K1 FIRST * CHARACTER
Terminator string : '|!?|'
K2 NEXT * CHARACTER
Terminator string : '|!?|'
GSMC NEXT * CHARACTER
Terminator string : '|!?|'
JGLX NEXT * CHARACTER
Terminator string : '|!?|'
KGS NEXT * CHARACTER
Terminator string : '|!?|'
RZS NEXT * CHARACTER
Terminator string : '|!?|'
ISJW NEXT * CHARACTER
Terminator string : '|!?|'
SYR NEXT * CHARACTER
Terminator string : '|!?|'
SYRLX NEXT * CHARACTER
Terminator string : '|!?|'
CGZB NEXT * CHARACTER
Terminator string : '|!?|'
TZLX NEXT * CHARACTER
Terminator string : '|!?|'
KGLJS NEXT * CHARACTER
Terminator string : '|!?|'
RZLX NEXT * CHARACTER
Terminator string : '|!?|'
BSM NEXT * CHARACTER
Terminator string : '|!?|'
Table XIAOXU.TEST_SQLLDR:
9415010 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: 3612000 bytes(1000 rows)
Read buffer bytes:10485760
Total logical records skipped: 0
Total logical records read: 9415010
Total logical records rejected: 0
Total logical records discarded: 0
Run began on Fri Nov 09 17:46:51 2018
Run ended on Fri Nov 09 17:57:06 2018
Elapsed time was: 00:10:15.28
CPU time was: 00:01:29.33
5 效率总结
在以上的结果中可以看出效率并不是很高，按以上计算9415010/615 = 15308 / S
如果把参数rows修改为10000 bindsize修改为804857600 ，用时是:00:08:37.85 ,
平均 9415010/517 = 18210 / S 看来改变参数有所改善效率 ，消息结果如下:
Space allocated for bind array: 36120000 bytes(10000 rows)
Read buffer bytes:804857600
Total logical records skipped: 0
Total logical records read: 9415010
Total logical records rejected: 0
Total logical records discarded: 0
Run began on Fri Nov 09 18:44:04 2018
Run ended on Fri Nov 09 18:52:42 2018
Elapsed time was: 00:08:37.85
CPU time was: 00:01:35.31