SQL Loader是一种数据加载工具,可以把外部数据加载到Oracle数据库中。SQL Loader中的参数有很多,本文只在第一部分中列出常用参数。要想运用SQL Loader工具 ,需要我们编辑一个控制文件,所谓控制文件就是告诉SQL Loader应该怎样去工作的文件,这很好理解。当我们在加载数据时可以用两种方式:一种是把数据写在控制文件中;另外一种是把数据单独存放在一个数据文件中,并在控制文件中标明此数据文件。
1.常用参数
参数名称 | 含义 |
userid | ORACLE username/password |
control | 控制文件,可能包含表的数据 |
log | 记录导入时的日志文件,默认为:控制文件.log |
bad | 坏数据文件,默认为:控制文件.bad |
data | 数据文件 |
discard | 丢弃的数据文件 |
discardmax | 允许丢弃的数据文件的最大值,默认全部 |
skip | 跳过的行数,默认为0 |
load | 导入的行数,默认全部 |
errors | 允许的错误记录条数,默认为50条 |
rows | 规定多少条记录后提交,默认为64条 |
silent | 禁止输出信息(header,feedback,errors,discards,partitions) tions) |
parfile | 参数文件,包含参数规范的文件名称 |
parallel | 并行导入,默认为FALSE |
##在操作系统中输入sqlldr,如:[oracle@lgr ~]$ sqlldr,可以获得更多参数。
2.控制文件
load data
infile 't_01.dat' -- 要导入的数据文件名称
--infile 't_02.dat' -- 如果是多个数据文件,那么可以在此处写入多个
--infile * -- 要导入的内容就在 CONTROL 文件里, BEGINDATA 后面就是导入的内容(与
上面格式不能同时使用)
insert:数据加载方式(默认)
加载方式有如下四种:
append:原先的表有数据就加在后面
insert:(默认值)装载空表,如果原先的表有数据 SQLLOADER 会停止
replace:原先的表有数据原先的数据会全部删除
truncate:指定的内容和 REPLACE 的相同会用 TRUNCATE 语句删除现存数据
badfile 'bf_name.bad':指定出现错误的记录存放的位置及名称。如果此参数没有指定,那么默认会
在控制文件同目录下生成一个与存放数据的文件同名的且后缀为 bad 的文件。
fields terminated by ',' optionally enclosed by '"'
转载的数据格式为,以','分隔的数据,且以'"'来标识一个字段的起始。主要是因为,在平文本文件中,
有可能出现带逗号的字段,那样,sqlloader 会误以为,那个逗号为分隔符,导致 load 的数据是错误的。
注:此参数可以声明也可以不声明,如果没声明,那么需要在定义字段的地方声明用什么来区分。
trailing nullcols:允许出现空值,当平文本文件中,没有对应表中字段的值,那么以 null 来代替。
如果不加此参数,那么,对应不上的记录将无法写入表,会出现在 bad 文件中。
(col_name1,col_name2,col_name3) :声明所有字段的名称。
如果没有声明 FIELDS TERMINATED BY ',',那么也可以在字段处进行声明,如下:
(
col_name1 [INTERGER EXTERNAL] TERMINATED BY ',' ,
col_name2 [DATE "DD-MON-YYY"] TERMINATED BY ',' ,
col_name3 [CHAR] TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
)
如果没有声明 FIELDS TERMINATED BY ',',并且文本文件中没有任何的分隔标识,那么也可以用指
定位置的方式来装载数据,如下:
(
col_name1 POSITION(1:2),
col_name2 POSITION(3:9),
col_name3 POSITION(*:15) CHAR(8),//char(8)指定字段类型及长度,*:15,表示,从上一个字
段结束的位置开始,15 结束
col_name4 POSITION(16:30) "TRIM(:col_name4)", // 去掉本字段截取的字符两边的空格
)
begindata:与 infile * 遥相呼应,即要导入的数据就在控制文件中,且在 begindata 的下面。
3.导入命令
编辑好控制文件后在操作系统中执行如下命令:
sqlldr user/password@dbservice control=file_name
##如果在本机导入数据,则登录时不用添加dbservice
4.友情提示
1) ROWS 的默认值为 64,你可以根据实际指定更合适的 ROWS 参数来指定每次提交记录数。
2)常规导入可以通过使用 INSERT语句来导入数据。Direct导入可以跳过数据库的相关逻辑(DIRECT=TRUE),而直接将数据导入到数据文件中,可以提高导入数据的性
能。 当然,在很多情况下,不能使用此参数(如果主键重复的话会使索引的状态变成UNUSABLE!)。
3) 通过指定 UNRECOVERABLE选项,可以关闭数据库的日志。这个选项只能和 direct 一起使用。
4) 对于超大数据文件的导入就要用并发操作了,即同时运行多个导入任务.
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 RESULTXT nologging;
这样不产生REDO LOG,可以提高效率。然后在 CONTROL 文件中 load data 上面加一行:unrecoverable, 此选项必须要与DIRECT共同应用。
5)一般只能用ASCII码形式,切记要转换编码,不然导入数据为空,ftp上传csv文件的传输类型选择ascii。
测试一 :infile *
1)创建目录对象,并赋权给scott用户
SYS@lgr> create or replace directory dir_dt as '/home/oracle';
Directory created.
SYS@lgr> grant read,write on directory dir_dt to scott;
Grant succeeded.
2)在scott用户下创建表,用于模拟向其中导入外部数据
SYS@lgr> conn scott/tiger
Connected.
SCOTT@lgr> create table sl_base (id number(5),fname varchar2(10),lname varchar2(10));
Table created.
SCOTT@lgr> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@lgr ~]$
3)创建控制文件
[oracle@lgr ~]$ vi base.ctl
load data infile * badfile 'base.bad' into table sl_base replace fields terminated by ',' (id,fname,lname) begindata 1,zhangfei,zhangyide 2,guanyu,guanyunchang 3,liubei,liuxuande |
4)通过sqlldr将控制文件中的数据导入sl_base表中
[oracle@lgr ~]$ sqlldr scott/tiger control=base.ctl
SQL*Loader: Release 11.2.0.4.0 - Production on Tue Jan 17 18:30:20 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 3
5)查看生成的相关日志
##我们在编辑控制文件时并没有指明日志文件路径,但我们可以在导入完成后查看到日志文件。其实,日志文件不用我们指定,它的默认路径是登录sqlldr的目录。可以利用日志查看数据的导入情况。
[oracle@lgr ~]$ ls
base.bad base.ctl base.log
6)查看生成的bad文件
[oracle@lgr ~]$ cat base.bad
2,guanyu,guanyunchang ##因为lname超过了sl_base表的字符长度,所以不能导入
7)查看sl_base 表的导入结果
SCOTT@lgr> select * from sl_base;
ID FNAME LNAME
---------- ---------- ----------
1 zhangfei zhangyide
3 liubei liuxuande
测试二 :null
要求:
- 利用测试一的目录对象,再次不再创建
- 创建存放数据的文件
- 使用默认的 bad 文件生成方式
- 使用 truncate 选项方式
1)清理测试一产生的文件
[oracle@lgr ~]$ rm base*
2)编辑模拟导入的数据文件
[oracle@lgr ~]$ vi base_data.dat
1,zhangfei,zhangyide 2,guanyu,guanyunchang 3,liubei,liuxuande 4,zhugeliang |
3)编辑控制文件
[oracle@lgr ~]$ vi base.ctl
load data infile 'base_data.dat' into table sl_base truncate fields terminated by ',' (id,fname,lname) |
4)通过sqlldr导入数据
[oracle@lgr ~]$ sqlldr scott/tiger control=base.ctl
SQL*Loader: Release 11.2.0.4.0 - Production on Tue Jan 17 18:52:27 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 4
5)查看生成的相关文件
[oracle@lgr ~]$ ll
total 20
-rw-r--r-- 1 oracle oinstall 103 Jan 17 18:50 base.ctl
-rw-r--r-- 1 oracle oinstall 35 Jan 17 18:52 base_data.bad
-rw-r--r-- 1 oracle oinstall 75 Jan 17 18:45 base_data.dat
-rw-r--r-- 1 oracle oinstall 1839 Jan 17 18:52 base.log
-rw-r--r-- 1 oracle oinstall 283 Jan 17 18:28 data.log
6)查看bad文件
[oracle@lgr ~]$ cat base_data.bad
4,zhugeliang
2,guanyu,guanyunchang
##bad文件中有两条错误记录,一条是因为字符长度问题,另外一条是因为lname中存在控制,而我们并没有对它进行处理。
7)数据库中查看sl_base表中数据
[oracle@lgr ~]$ sqlplus scott/tiger
SQL*Plus: Release 11.2.0.4.0 Production on Tue Jan 17 18:57:54 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SCOTT@lgr> select * from sl_base;
ID FNAME LNAME
---------- ---------- ----------
1 zhangfei zhangyide
3 liubei liuxuande
8)对空值进行处理,重新编辑控制文件
[oracle@lgr ~]$ vi base.ctl
load data infile 'base_data.dat' into table sl_base truncate fields terminated by ',' trailing nullcols (id,fname,lname) |
9)利用sqlldr重新导入数据
[oracle@lgr ~]$ sqlldr scott/tiger control=base.ctl
SQL*Loader: Release 11.2.0.4.0 - Production on Tue Jan 17 19:02:33 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 4
10)再次查看bad文件
[oracle@lgr ~]$ cat base_data.bad
2,guanyu,guanyunchang
11)再次查看表sl_base中数据
[oracle@lgr ~]$ sqlplus scott/tiger
SQL*Plus: Release 11.2.0.4.0 Production on Tue Jan 17 19:03:58 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SCOTT@lgr> select * from sl_base;
ID FNAME LNAME
---------- ---------- ----------
1 zhangfei zhangyide
3 liubei liuxuande
4 zhugeliang
12) 小结
如果数据文件中的数据存在 null 值,那么一定记得在控制文件中加入 trailing nullcols 语句,
除非你不想导入 null 值。
测试三 :字符串中包含逗号
要求:
- 数据文件中的数据存在逗号
- 在控制文件中定义字段时指定分隔符
1)删除上次实验相关文件
[oracle@lgr ~]$ rm base* data*
2)创建数据文件
[oracle@lgr ~]$ vi base_data.bat
1,zhangfei,"zhang,yide" 2,guanyu,"guan,yunchang" 3,liubei,"liu,xuande" 4,zhugeliang |
3)创建控制文件
[oracle@lgr ~]$ vi base.ctl
load data infile 'base_data.bat' into table sl_base truncate trailing nullcols ( id terminated by ',', fname terminated by ',', lname terminated by ',' optionally enclosed by '"' ) |
4)利用sqlldr导入数据
[oracle@lgr ~]$ sqlldr scott/tiger control=base.ctl
SQL*Loader: Release 11.2.0.4.0 - Production on Tue Jan 17 19:18:54 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 4
5)查看生成的相关文件
[oracle@lgr ~]$ ls
base.ctl base_data.bad base_data.bat base.log
6)查看bad文件,还是那条因为字符长度不合要求的数据
[oracle@lgr ~]$ cat base_data.bad
2,guanyu,"guan,yunchang"
7)查看sl_base表的数据
[oracle@lgr ~]$ sqlplus scott/tiger
SQL*Plus: Release 11.2.0.4.0 Production on Tue Jan 17 19:19:35 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SCOTT@lgr> select * from sl_base;
ID FNAME LNAME
---------- ---------- ----------
1 zhangfei zhang,yide
3 liubei liu,xuande
4 zhugeliang
测试四 :数据文件中无分隔符
1)删除上次实验生成的文件
[oracle@lgr ~]$ rm base*
2)创建数据文件
[oracle@lgr ~]$ vi base_data.bat
1zhangfei zhangyide 2guanyu guanyuchang 3liubei liuxuande 4zhugeliang |
3)创建控制文件
[oracle@lgr ~]$ vi base.ctl
load data infile 'base_data.bat' into table sl_base truncate trailing nullcols ( id position(1:1), fname position(2:11), lname position(12:22) ) |
4)利用sqlldr导入数据
[oracle@lgr ~]$ sqlldr scott/tiger control=base.ctl
SQL*Loader: Release 11.2.0.4.0 - Production on Tue Jan 17 19:43:42 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 4
5)查看生成的相关文件
[oracle@lgr ~]$ ls
base.ctl base_data.bad base_data.bat base_data.log base.log
6)查看bad文件
[oracle@lgr ~]$ cat base_data.bad
2guanyu guanyuchang
7)验证导入结果
[oracle@lgr ~]$ sqlplus scott/tiger
SQL*Plus: Release 11.2.0.4.0 Production on Tue Jan 17 19:45:17 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SCOTT@lgr> select * from sl_base;
ID FNAME LNAME
---------- ---------- ----------
1 zhangfei zhangyide
3 liubei liuxuande
4 zhugeliang