要求:
①指定bad文件;
②挂在之前将目标表delete;
③导入的的数据在控制文件中。
1.创建目录对象:
13:05:24 SYS@ORA11GR2>create or replace directory dir_dt as '/home/oracle'; Directory created.
2.将读写权限给scott用户:
13:05:28 SYS@ORA11GR2>grant read,write on directory dir_dt to scott;
Grant succeeded.
3.scott用户下创建测试表:
13:07:17 SYS@ORA11GR2>conn scott/tiger
Connected.
13:07:57 SCOTT@ORA11GR2>create table s1_base(id number(5),fname varchar2(10),lname varchar2(10)); Table created.
4.创建控制文件:
[oracle@host03 ~]$ vi base.ctl
load data
infile *
badfile 'base.bad'
into table s1_base
replace
fields terminated by ','
(id,fname,lname)
begindata
1,zhangfei,zhangyunde
2,guanyun,guanyunchang
3,liubei,liuxuande
5.通过sqlldr将控制文件的数据导入scott用户下的测试表
[oracle@host03 ~]$ sqlldr scott/tiger control=base.ctl SQL*Loader: Release 11.2.0.4.0 - Production on Fri Nov 11 13:09:59 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Commit point reached - logical record count 3
6.查看生成的相关日志:
[oracle@host03 ~]$ ll
total 12
-rw-r--r-- 1 oracle oinstall 23 Nov 11 13:10 base.bad
-rw-r--r-- 1 oracle oinstall 181 Nov 11 13:09 base.ctl
-rw-r--r-- 1 oracle oinstall 1698 Nov 11 13:10 base.log
7.查看bad文件:
[oracle@host03 ~]$ cat base.bad
2,guanyun,guanyunchang
8.查看导入数据:
13:08:39 SCOTT@ORA11GR2>select * from s1_base; ID FNAME LNAME
---------- ---------- ----------
1 zhangfei zhangyunde
3 liubei liuxuande