1.创建控制文件:
[oracle@host03 ~]$ vi base.ctl
load data
infile 'base_data.dat'
into table s1_base
truncate
trailing nullcols
(
id terminated by ',',
fname terminated by ',',
lname terminated by ','optionally enclosed by '"'
)
2.创建数据文件:
[oracle@host03 ~]$ vi base_data.dat
1,zhangfei,"zhang,yide"
2,guanyu,"guan,yunchang"
3,liubei,"liu,xuande"
4,zhugeliang
3.执行sqlldr:
[oracle@host03 ~]$ sqlldr scott/tiger control=base.ctl
SQL*Loader: Release 11.2.0.4.0 - Production on Tue Dec 13 21:37:12 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Commit point reached - logical record count 4
4.查看执行后当前目录生成的文件:
[oracle@host03 ~]$ ls
base.ctl base_data.bad base_data.dat base.log
5.查看bad文件,还是那条过长的记录没有导入:
[oracle@host03 ~]$ cat base_data.bad
2,guanyu,"guan,yunchang"
6.查看导入记录:
21:40:00 SYS@ORA11GR2>conn scott/tiger
Connected.
21:40:38 SCOTT@ORA11GR2>select * from s1_base;
ID FNAME LNAME
---------- ---------- ----------
1 zhangfei zhang,yide
3 liubei liu,xuande
4 zhugeliang