oracle sql loader

SQL*LOADER是ORACLE的数据加载工具，通常用来将操作系统文件迁移到ORACLE数据库中。SQL*LOADER是大型数据
仓库选择使用的加载方法。

　　在NT下，SQL*LOADER的命令为SQLLDR，在UNIX下一般为sqlldr/sqlload。
　　如执行：d:/oracle>sqlldr
SQL*Loader: Release 8.1.6.0.0 - Production on 星期二 1月 8 11:06:42 2002
(c) Copyright 1999 Oracle Corporation. All rights reserved.

用法: SQLLOAD 关键字 = 值 [,keyword=value,...]
有效的关键字:
userid -- ORACLE username/password
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 (全部默认)
skip -- Number of logical records to skip (默认0)
load -- Number of logical records to load (全部默认)
errors -- Number of errors to allow (默认50)
rows -- Number of rows in conventional path bind array or between direct p
ath data saves
（默认: 常规路径 64, 所有直接路径）
bindsize -- Size of conventional path bind array in bytes(默认65536)
silent -- Suppress messages during run (header,feedback,errors,discards,part
itions)
direct -- use direct path (默认FALSE)
parfile -- parameter file: name of file that contains parameter specification
s
parallel -- do parallel load (默认FALSE)
file -- File to allocate extents from
skip_unusable_indexes -- disallow/allow unusable indexes or index partitions(默
认FALSE)
skip_index_maintenance -- do not maintain indexes, mark affected indexes as unus
able(默认FALSE)
commit_discontinued -- commit loaded rows when load is discontinued(默认FALSE)
readsize -- Size of Read buffer (默认1048576)
PLEASE NOTE: 命令行参数可以由位置或关键字指定
。前者的例子是 'sqlload
scott/tiger foo';后者的例子是 'sqlload control=foo
userid=scott/tiger'.位置指定参数的时间必须早于
但不可迟于由关键字指定的参数。例如,
'SQLLOAD SCott/tiger control=foo logfile=log', 但
'不允许 sqlload scott/tiger control=foo log',即使允许
参数 'log' 的位置正确。

控制文件介绍
LOAD DATA
INFILE 't.dat' // 要导入的文件
// INFILE 'tt.date' // 导入多个文件
// INFILE * // 要导入的内容就在control文件里 下面的BEGINDATA后面就是导入的内容, *和't.dat'不能同时存在

INTO TABLE table_name // 指定装入的表
BADFILE 'c:bad.txt' // 指定坏文件地址

************* 以下是4种装入表的方式
APPEND // 原先的表有数据 就加在后面
// INSERT // 装载空表 如果原先的表有数据 sqlloader会停止 默认值
// REPLACE // 原先的表有数据 原先的数据会全部删除
// TRUNCATE // 指定的内容和replace的相同 会用truncate语句删除现存数据

************* 指定的TERMINATED可以在表的开头 也可在表的内部字段部分
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
// 装载这种数据: 10,lg,"""lg""","lg,lg"
// 在表中结果: 10 lg "lg" lg,lg
// TERMINATED BY X '09' // 以十六进制格式 '09' 表示的
// TERMINATED BY WRITESPACE // 装载这种数据: 10 lg lg

TRAILING NULLCOLS ************* 表的字段没有对应的值时允许为空

************* 下面是表的字段
(
col_1 , col_2 ,col_filler FILLER // FILLER 关键字 此列的数值不会被装载
// 如: lg,lg,not 结果 lg lg
)
// 当没声明FIELDS TERMINATED BY ',' 时
// (
// col_1 [interger external] TERMINATED BY ',' ,
// col_2 [date "dd-mon-yyy"] TERMINATED BY ',' ,
// col_3 [char] TERMINATED BY ',' OPTIONALLY ENCLOSED BY 'lg'
// )
// 当没声明FIELDS TERMINATED BY ','用位置告诉字段装载数据
// (
// col_1 position(1:2),
// col_2 position(3:10),
// col_3 position(*:16), // 这个字段的开始位置在前一字段的结束位置
// col_4 position(1:16),
// col_5 position(3:10) char(8) // 指定字段的类型
// )

看晕了吧？呵呵，我一开始也看得晕晕的，那就从我的实例开始吧！
这里，我的操作系统是xp，数据库主机是unix，数据库是oracle 10g。现要将我的机器里的文本文件datafile.txt导入到远程数据库中，
现在文件里的记录数达几千万。
datafile.txt文本内容：
js1a,192.168.2.254:80:,RUNNING
other,192.168.2.254:80:test.com,STOPPED
third,192.168.2.254:81:thirdabc.com,RUNNING
……
从中，我们看出有3列，分别以逗号分隔，为变长字符串。
首先，我们现在数据库建好表
create table mydata
(
mac_name varchar2(10),
mac_ip varchar2(50),
status varchar2(10)
)

现在我们先整理控制文件myldr.ctl(文件名可以自定义)
myldr.ctl内容：
load data
infile 'datafile.txt'
into table mydata
(mac_name char terminated by ',',
mac_ip char terminated by ',',
status char terminated by whitespace)

说明：
infile　指数据源文件　这里我们省略了默认的　discardfile result.dsc badfile result.bad
into table mydata 默认是INSERT，也可以into table mydata APPEND为追加方式，或REPLACE
terminated by ','　指用逗号分隔
terminated by whitespace　结尾以空白分隔

此时我们执行加载：
D:/>sqlldr userid=sdsettle/stl123@js1a control=myldr.ctl log=ldr.log

一开始我用的是plsql dev的文本导入器，那数据慢的，人都快崩溃了，平均一秒才导入100条数据，一千多万的数据，天啊！
所以为了快点完成工作(其实是为了早点下班，嘿嘿)，所以想到了用sql loader，以前只是听说这个导数据快，还没用过，所以赶紧
拿这个试试，用了以后那效果，真的是谁用谁知道啊！原来导入估计要30多个小时，现在不到一个小时就搞定了！


据说还可以并发操作，这个我没实验
sqlldr userid=/ control=result1.ctl direct=true parallel=true
sqlldr userid=/ control=result2.ctl direct=true parallel=true
sqlldr userid=/ control=result2.ctl direct=true parallel=true
当加载大量数据时（大约超过10GB），最好抑制日志的产生：
SQL>ALTER TABLE mydata nologging;
这样不产生REDO　LOG，可以提高效率。然后在CONTROL文件中load data上面加一行：unrecoverable
此选项必须要与DIRECT共同应用。
在并发操作时，ORACLE声称可以达到每小时处理100GB数据的能力！其实，估计能到1－10G就算不错了，开始可用结构
相同的文件，但只有少量数据，成功后开始加载大量数据，这样可以避免时间的浪费。

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15663454/viewspace-1056466/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15663454/viewspace-1056466/