SQL*Loader
SQL*LOADER是ORACLE的数据加载工具，通常用来将操作系统文件迁移到ORACLE数据库中，其加载的文件为文本文件，可以查看！！！
1. 常用参数
| 参数名称 | 含义 |
| userid | ORACLE username/password |
| control | 控制文件 |
| log | 记录的日志文件 |
| bad | 坏数据文件 |
| data | 数据文件 |
| discard | 丢弃的数据文件 |
| discardmax | 允许丢弃数据的最大值(全部默认) |
| skip | Number of logical records to skip(默认 0) |
| load | Number of logical records to load(全部默认) |
| errors | 允许的错误记录数(默认 50) |
| rows | Number of rows in conventional path bind array or between |
| bindsize | Size of conventional path bind array in bytes(默认 256000)每次提交记录的缓冲区的大小(字节为单位，默认 256000) |
| silent | 禁止输出信息 (header,feedback,errors,discards,partitions) |
| direct | 使用直通路径方式导入(默认 FALSE) |
| parfile | parameter file: name of file that contains parameter specifications |
| parallel | 并行导入 (默认 FALSE) |
| | |
注 在操作系统中直接输入 sqlldr 即可查看相关参数的帮助信息 [oracle@ocmu ~]$ 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. 测试一： infile *
要求：
- 指定 bad 文件
- 装载之前将目标表 delete 清空
- 导入的数据在控制文件中
1) 在 scott 用户下创建测试表
SCOTT@ORA11GR2>create table sl_base(id number(5),fname varchar2(10),lname varchar2(10));
Table created.
2) 创建控制文件
[oracle@wang ~]$ 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
~
"base.ctl" [New] 11L, 179C written
[oracle@wang ~]$
3) 通过 sqlldr 将控制文件的数据导入 scott 用户下的测试表
[oracle@wang ~]$ sqlldr scott/tiger control=base.ctl
SQL*Loader: Release 11.2.0.4.0 - Production on Fri Sep 23 21:32:08 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 3
[oracle@wang ~]$
4) 查看所生成的相关日志
[oracle@wang ~]$ ls
base.bad base.ctl base.log
[oracle@wang ~]$
[oracle@wang ~]$ ll
total 12
-rw-r--r-- 1 oracle oinstall 22 Sep 23 21:32 base.bad
-rw-r--r-- 1 oracle oinstall 179 Sep 23 21:31 base.ctl
-rw-r--r-- 1 oracle oinstall 1698 Sep 23 21:32 base.log
[oracle@wang ~]$
5) 查看生成的 bad 文件
[oracle@wang ~]$ cat base.bad
2,guanyu,guanyunchang
[oracle@wang ~]$
6) 通过 sqlplus 查看导入的数据
SCOTT@ORA11GR2>select * from sl_base;
ID FNAME LNAME
---------- ---------- ----------
1 zhangfei zhangyide
3 liubei liuxuande
7) 小结
本测试是将数据写入控制文件中，未指定 bad 文件，由于控制文件中的数据长度大于测试表的字段长度，导致导入失败，默认的情况下自动生成 bad 文件，文件的存放目录为执行 sqlldr 的目录，另由于未指定日志文件目录，所以，也自动生成到 sqlldr 目录下， bad 文件和 log 文件的默认名称与控制文件的名称相同。
4. 测试二： null 值
要求：
- 创建存放数据的文件
- 使用默认的 bad 文件生成方式
- 使用 truncate 选项方式
1) 清理环境测试一生成的文件
[oracle@wang ~]$ rm base*
[oracle@wang ~]$
[oracle@wang ~]$ ls
[oracle@wang ~]$
2) 创建数据文件
[oracle@wang ~]$ vi base_data.dat
1,zhangfei,zhangyide
2,guanyu,guanyunchang
3,liubei,liuxuande
4,kongming
~~
"base_data.dat" [New] 4L, 73C written
[oracle@wang ~]$
3) 创建控制文件
[oracle@wang ~]$ vi base.ctl
load data
infile 'base_data.dat'
into table sl_base
truncate
fields terminated by ','
(id,fname,lname)
~
"base.ctl" [New] 6L, 103C written
[oracle@wang ~]$
4) 查看创建的文件
[oracle@wang ~]$ ll
total 8
-rw-r--r-- 1 oracle oinstall 103 Sep 23 21:45 base.ctl
-rw-r--r-- 1 oracle oinstall 73 Sep 23 21:42 base_data.dat
[oracle@wang ~]$
5) 通过 sqlldr 导入数据
[oracle@wang ~]$ sqlldr scott/tiger control=base.ctl
SQL*Loader: Release 11.2.0.4.0 - Production on Fri Sep 23 21:47:12 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 4
[oracle@wang ~]$
6) 查看生成的相关文件
[oracle@wang ~]$ ll
total 16
-rw-r--r-- 1 oracle oinstall 103 Sep 23 21:45 base.ctl
-rw-r--r-- 1 oracle oinstall 33 Sep 23 21:47 base_data.bad
-rw-r--r-- 1 oracle oinstall 73 Sep 23 21:42 base_data.dat
-rw-r--r-- 1 oracle oinstall 1839 Sep 23 21:47 base.log
[oracle@wang ~]$
7) 查看 bad 文件内容
[oracle@wang ~]$ cat base_data.bad
4,kongming
2,guanyu,guanyunchang
[oracle@wang ~]$
注：bad 文件中有两条记录， id 为 2 的记录未导入的原因是 lname 字段太长，id 为 4 的记录未导
入原因是 lname 字段出现了空值
8) 通过 sqlplus 查看导入信息
SCOTT@ORA11GR2>select * from sl_base;
ID FNAME LNAME
---------- ---------- ----------
1 zhangfei zhangyide
3 liubei liuxuande
9) 解决 null 值问题，编辑控制文件
[oracle@wang ~]$ ls
base.ctl base_data.bad base_data.dat base.log
[oracle@wang ~]$
[oracle@wang ~]$ vi base.ctl
load data
infile 'base_data.dat'
into table sl_base
truncate
fields terminated by ','
trailing nullcols
(id,fname,lname)
~
"base.ctl" 7L, 121C written
[oracle@wang ~]$
10) 再次执行 sqlldr
[oracle@wang ~]$ sqlldr scott/tiger control=base.ctl
SQL*Loader: Release 11.2.0.4.0 - Production on Fri Sep 23 21:54:20 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 4
[oracle@wang ~]$
11) 查看生成文件
[oracle@wang ~]$ ll
total 16
-rw-r--r-- 1 oracle oinstall 121 Sep 23 21:53 base.ctl
-rw-r--r-- 1 oracle oinstall 22 Sep 23 21:54 base_data.bad
-rw-r--r-- 1 oracle oinstall 73 Sep 23 21:42 base_data.dat
-rw-r--r-- 1 oracle oinstall 1744 Sep 23 21:54 base.log
[oracle@wang ~]$
12) 查看 bad 文件，此时空值错误的记录已经不存在了
[oracle@wang ~]$ cat base_data.bad
2,guanyu,guanyunchang
[oracle@wang ~]$
13) 验证空值
SCOTT@ORA11GR2>select * from sl_base;
ID FNAME LNAME
---------- ---------- ----------
1 zhangfei zhangyide
3 liubei liuxuande
4 kongming
14) 小结：
如果数据文件中的数据存在 null 值，那么一定记得在控制文件中加入 trailing nullcols 语句，
除非你不想导入 null 值。
5. 测试三：字符串中包含逗号
要求：
- 数据文件中的数据存在逗号
- 在控制文件中定义字段时指定分隔符
1) 删除上次实验相关文件
[oracle@wang ~]$ ls
base.ctl base_data.bad base_data.dat base.log
[oracle@wang ~]$
[oracle@wang ~]$
[oracle@wang ~]$ rm *
[oracle@wang ~]$ ls
[oracle@wang ~]$
2) 创建控制文件
[oracle@wang ~]$ vi base.ctl
load data
infile 'base_data.dat'
into table sl_base
truncate
trailing nullcols
(id terminated by ',',
fname terminated by ',',
lname terminated by ','optionally enclosed by '"'
)
~
"base.ctl" [New] 9L, 179C written
[oracle@wang ~]$
3) 创建数据文件
[oracle@wang ~]$ vi base_data.dat
1,zhangfei,"zhang,yide"
2,guanyu,"guan,yunchang"
3,liubei,"liu,xuande"
4,kongming
~
"base_data.dat" [New] 4L, 82C written
[oracle@wang ~]$
4) 执行 sqlldr
[oracle@wang ~]$ sqlldr scott/tiger control=base.ctl
SQL*Loader: Release 11.2.0.4.0 - Production on Fri Sep 23 22:08:15 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 4
[oracle@wang ~]$
5) 查看执行后当前目录生成的文件
[oracle@wang ~]$ ls
base.ctl base_data.bad base_data.dat base.log
[oracle@wang ~]$
6) 查看 bad 文件，还是那条过长的记录没有导入
[oracle@wang ~]$ cat base_data.bad
2,guanyu,"guan,yunchang"
[oracle@wang ~]$
7) 验证
SCOTT@ORA11GR2>select * from sl_base;
ID FNAME LNAME
---------- ---------- ----------
1 zhangfei zhang,yide
3 liubei liu,xuande
4 kongming
6. 测试四：数据文件中无分隔符
1) 删除上次实验相关文件
[oracle@wang ~]$ ls
base.ctl base_data.bad base_data.dat base.log
[oracle@wang ~]$
[oracle@wang ~]$ rm *
[oracle@wang ~]$
[oracle@wang ~]$ ls
[oracle@wang ~]$
2) 创建数据文件
[oracle@wang ~]$ vi base_data.dat
1zhangfeizhangyide
2guanyu guanyunchang
3liubei liuxuande
4kongming
~
"base_data.dat" [New] 4L, 70C written
[oracle@wang ~]$
[oracle@wang ~]$ ls
base_data.dat
[oracle@wang ~]$
3) 创建控制文件
[oracle@wang ~]$ vi base.ctl
load data
infile 'base_data.dat'
into table sl_base
truncate
trailing nullcols
(id position(1:1),
fname position(2:9),
lname position(10:22))
~
"base.ctl" [New] 8L, 142C written
[oracle@wang ~]$
4) 执行 sqlldr
[oracle@wang ~]$ sqlldr scott/tiger control=base.ctl
SQL*Loader: Release 11.2.0.4.0 - Production on Fri Sep 23 22:18:00 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 4
[oracle@wang ~]$
5) 查看生成的文件
[oracle@wang ~]$ ls
base.ctl base_data.bad base_data.dat base.log
[oracle@wang ~]$
查看bad日志：
[oracle@wang ~]$ cat base_data.bad
2guanyu guanyunchang
[oracle@wang ~]$
6) 验证
SCOTT@ORA11GR2>select * from sl_base;
ID FNAME LNAME
---------- ---------- ----------
1 zhangfei zhangyide
3 liubei liuxuande
4 kongming
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31397003/viewspace-2126617/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/31397003/viewspace-2126617/
2329
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
