oracle sql loader

pengjunling

　　SQL*LOADER是ORACLE的数据加载工具，通常用来将操作系统文件迁移到ORACLE数据库中。SQL*LOADER是大型数据
　　仓库选择使用的加载方法。
　　在NT下，SQL*LOADER的命令为SQLLDR，在UNIX下一般为sqlldr/sqlload。
　　如执行：d:/oracle>sqlldr

　　SQL*Loader:>　　(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 -->　　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 -->　　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就算不错了，开始可用结构
　　相同的文件，但只有少量数据，成功后开始加载大量数据，这样可以避免时间的浪费。