sqlldr总结

最新推荐文章于 2023-05-16 14:13:13 发布

转载最新推荐文章于 2023-05-16 14:13:13 发布 · 789 阅读

ORACLE 专栏收录该内容

13 篇文章

订阅专栏

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
&lt; 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