sqlldr总结

1． SQL*LOADER 是 ORACLE 的数据加载工具，通常用来将操作系统文件迁移到 ORACLE 数据库中。

2． 在 NT 下， SQL*LOADER 的命令为 SQLLDR ，在 UNIX 下一般为 sqlldr/sqlload （在 /oracle/product/10.2.0/bin 目录下可以看到存在 sqlldr ） 。

3． 用法 : sqlldr 关键字 = 值 [,keyword=value,...]

4． 当加载大量数据时，可以抑制日志的产生：

a) ALTER TABLE RESULTXT nologging;

5． 控制文件 .ctl

load data 　　　　　　　　　　 --1 、控制文件标识

infile 'test.txt' 　　　　　　 --2 、要输入的数据文件名为 test.txt

append into table test 　　　　 --3 、向表 test 中追加记录

fields terminated by '|+|' TRAILING NULLCOLS 　　 --4 、字段终止于空行

(TP_CUSTOMER_ID ,

ECIF_NO ,

TP_SYSTEM_SOURCE_CD) 　　 ----- 定义列对应顺序

其中 append 为数据装载方式，还有其他选项：

a 、 insert ，为缺省方式，在数据装载开始时要求表为空

b 、 append ，在表中追加新记录

c 、 replace ，删除旧记录（全部的记录），替换成新装载的记录

d 、 truncate ，同上 先删除再新增

6． 范例：

a) Unix + Oracle 环境。

b) 在某目录下准备好 test.ctl 和 test.dat 文件。

c) 运行命令 : sqlldr userid=orcl /orcle@10.1.1.11:1521/orcl control=test.ctl data=test.dat log=test.log bad=test.bad;

7． sqlldr 用到的主要参数

1) userid -- ORACLE username/password

2) control – 控制文件

3) log – 记录的日志文件

4) bad – 坏数据文件，记录错误的未加载数据

5) data – 数据文件 ,* data 参数只能指定一个数据文件 , 如果控制文件也通过 infile 指定了数据文件 , 并且指定多个 , 则 sqlldr 在执行时 , 先加载 data 参数指定的数据文件 , 控制文件中第一个 infile 指定的数据文件被忽略 , 但后续的 infile 指定的数据文件继续有效

6) discard – 丢弃的数据文件

7) discardmax – 允许丢弃数据的最大值 ( 默认全部 )

8) skip -- 跳过记录数 , 从数据文件中 , 从第一行开始要计算要跳过的行数 ( 默认 0)

9) load -- Number of logical records to load ( 默认全部 )

10) errors – 允许的错误记录数 , 超过则终止任务 ( 默认 50)

11) rows -- Number of rows in conventional path bind array or between direct path data saves （每次提交的记录数，默认 : 常规路径 64, 直接路径 全部，所以使用直接路径的话，效率会比普通的好太多太多）

12) bindsize -- Size of conventional path bind array in bytes （ 每次提交记录的缓冲区的大小，字节为单位，默认 256000)

13) silent -- 禁止输出信息 (header,feedback,errors,discards,partitions)

14) direct –使用直通路径方式导入(默认FALSE)

如果表中有索引的话，是不能指定direct=TRUE的 ，除非使用 skip_index_maintenance=TRUE ，这个就是在导入的时候忽略索引，所以在数据导入完毕以后，查看索引的状态应该都是无效的，需要重建之 , 如下 SQL ： select * from dba_indexes where table_name='?' ;

alter idnex index_name rebuild ;

重新建立索引要比新建索引快。

15) parallel --并行导入 ( 默认 FALSE ，注意： parallel 并不是让一个 sqlldr 语句起多个进程来加载数据 , 而是不锁住加载表 , 允许别的直接路径加载 . 所以要使 parallel 起作用 , 应该先将要加载的数据文件分成多个 , 用多个 sqlldr 语句同时加载 , 如下例 :
sqlldr userid=scott/tiger control=load1.ctl data=data1.txt direct=y parallel=true & sqlldr userid=scott/tiger control=load2.ctl data=data2.txt direct=y parallel=true & sqlldr userid=scott/tiger control=load3.ctl data=data3.txt direct=y parallel=true &)

16) skip_unusable_indexes -- disallow/allow unusable indexes or index partitions( 默认 FALSE)

17) skip_index_maintenance -- do not maintain indexes, mark affected indexes as unusable( 默认 FALSE)

 

 

=================================================================================== 
  注意begindata后的数值前面不能有空格  

1   *****   普通装载  
LOAD   DATA  
INFILE   *  
INTO   TABLE   DEPT  
REPLACE  
FIELDS   TERMINATED   BY   ', '   OPTIONALLY   ENCLOSED   BY   '" '  
(DEPTNO,  
DNAME,  
LOC  
)  
BEGINDATA  
10,Sales,"""USA"""  
20,Accounting,"Virginia,USA"  
30,Consulting,Virginia  
40,Finance,Virginia  
50,"Finance","",Virginia   //   loc   列将为空  
60,"Finance",,Virginia   //   loc   列将为空  

2   *****   FIELDS   TERMINATED   BY   WHITESPACE   和   FIELDS   TERMINATED   BY   x '09 '   的情况  
LOAD   DATA  
INFILE   *  
INTO   TABLE   DEPT  
REPLACE  
FIELDS   TERMINATED   BY   WHITESPACE  
--   FIELDS   TERMINATED   BY   x '09 '  
(DEPTNO,  
DNAME,  
LOC  
)  
BEGINDATA  
10   Sales   Virginia  

3   *****   指定不装载那一列  
LOAD   DATA  
INFILE   *  
INTO   TABLE   DEPT  
REPLACE  
FIELDS   TERMINATED   BY   ', '   OPTIONALLY   ENCLOSED   BY   '" '  
(   DEPTNO,  
FILLER_1   FILLER,   //   下面的   "Something   Not   To   Be   Loaded"   将不会被装载  
DNAME,  
LOC  
)  
BEGINDATA  
20,Something   Not   To   Be   Loaded,Accounting,"Virginia,USA"  

4   *****   position的列子  
LOAD   DATA  
INFILE   *  
INTO   TABLE   DEPT  
REPLACE  
(   DEPTNO   position(1:2),  
DNAME   position(*:16),   //   这个字段的开始位置在前一字段的结束位置  
LOC   position(*:29),  
ENTIRE_LINE   position(1:29)  
)  
BEGINDATA  
10Accounting   Virginia,USA  

5   *****   使用函数   日期的一种表达   TRAILING   NULLCOLS的使用  
LOAD   DATA  
INFILE   *  
INTO   TABLE   DEPT  
REPLACE  
FIELDS   TERMINATED   BY   ', '  
TRAILING   NULLCOLS   //   其实下面的ENTIRE_LINE在BEGINDATA后面的数据中是没有直接对应  
//   的列的值的   如果第一行改为   10,Sales,Virginia,1/5/2000,,   就不用TRAILING   NULLCOLS了  
(DEPTNO,  
DNAME   "upper(:dname)",   //   使用函数  
LOC   "upper(:loc)",  
LAST_UPDATED   date   'dd/mm/yyyy ',   //   日期的一种表达方式   还有 'dd-mon-yyyy '   等  
ENTIRE_LINE   ":deptno||:dname||:loc||:last_updated"  
)  
BEGINDATA  
10,Sales,Virginia,1/5/2000  
20,Accounting,Virginia,21/6/1999  
30,Consulting,Virginia,5/1/2000  
40,Finance,Virginia,15/3/2001  
LOAD   DATA  
INFILE   *  
concatenate   3   //   通过关键字concatenate   把几行的记录看成一行记录  
INTO   TABLE   DEPT  
replace  
FIELDS   TERMINATED   BY   ', '  
(DEPTNO,  
DNAME   "upper(:dname)",  
LOC   "upper(:loc)",  
LAST_UPDATED   date   'dd/mm/yyyy '  
)  
BEGINDATA  
10,Sales,   //   其实这3行看成一行   10,Sales,Virginia,1/5/2000  
Virginia,  
1/5/2000  
//   这列子用   continueif   list=","   也可以  
告诉sqlldr在每行的末尾找逗号   找到逗号就把下一行附加到上一行  

LOAD   DATA  
INFILE   *  
continueif   this(1:1)   =   '- '   //   找每行的开始是否有连接字符   -   有就把下一行连接为一行  
//   如   -10,Sales,Virginia,  
//   1/5/2000   就是一行   10,Sales,Virginia,1/5/2000  
//   其中1:1   表示从第一行开始   并在第一行结束   还有continueif   next   但continueif   list最理想  
INTO   TABLE   DEPT  
replace  
FIELDS   TERMINATED   BY   ', '  
(DEPTNO,  
DNAME   "upper(:dname)",  
LOC   "upper(:loc)",  
LAST_UPDATED   date   'dd/mm/yyyy '  
)  
BEGINDATA   //   但是好象不能象右面的那样使用  
-10,Sales,Virginia,   -10,Sales,Virginia,  
1/5/2000   1/5/2000  
-40,   40,Finance,Virginia,13/04/2001  
Finance,Virginia,13/04/2001  

8   *****   载入每行的行号  

load   data  
infile   *  
into   table   t  
replace  
(   seqno   RECNUM   //载入每行的行号  
text   Position(1:1024))  
BEGINDATA  
fsdfasj   //自动分配一行号给载入   表t   的seqno字段   此行为   1  
fasdjfasdfl   //   此行为   2   ...  

9   *****   载入有换行符的数据  
注意:   unix   和   windows   不同   \\n   &   /n  
<   1   >   使用一个非换行符的字符  
LOAD   DATA  
INFILE   *  
INTO   TABLE   DEPT  
REPLACE  
FIELDS   TERMINATED   BY   ', '  
TRAILING   NULLCOLS  
(DEPTNO,  
DNAME   "upper(:dname)",  
LOC   "upper(:loc)",  
LAST_UPDATED   "my_to_date(   :last_updated   )",  
COMMENTS   "replace(:comments, '\n ',chr(10))"   //   replace   的使用帮助转换换行符  
)  
BEGINDATA  
10,Sales,Virginia,01-april-2001,This   is   the   Sales\nOffice   in   Virginia  
20,Accounting,Virginia,13/04/2001,This   is   the   Accounting\nOffice   in   Virginia  
30,Consulting,Virginia,14/04/2001   12:02:02,This   is   the   Consulting\nOffice   in   Virginia  
40,Finance,Virginia,987268297,This   is   the   Finance\nOffice   in   Virginia