创建外部表

一，先在操作系统中创建目录 data,bad,log,dis
以下是外部文件中的记录

20080629,修改,1301110022,邹雪辉,01110022
20080629,修改,1302050023,王晓斌,02050023
20080629,修改,1306060130,邵静,06060130
20080629,修改,1304020386,张晋,04020386
20080629,修改,1301070082,许征,01070082

二，在数据库中创建目录

SQL> create or replace directory data_log as 'D:\oracle\data';--用于存放外部表数据文件

Directory created

SQL> create or replace directory bad_log as 'D:\oracle\bad';--用于存放查询外部表时错误的文件

Directory created

SQL> create or replace directory dis_log as 'D:\oracle\dis';

Directory created

SQL> create or replace directory log_log as 'D:\oracle\log';--用于存放日志文件
授予读写权限
SQL> grant read,write on directory data_dir to insur_changde;

Grant succeeded

SQL> grant read,write on directory bad_dir to insur_changde;

Grant succeeded

SQL> grant read,write on directory dis_dir to insur_changde;

Grant succeeded

SQL> grant read,write on directory log_dir to insur_changde;

Grant succeeded

三.创建外部表

create table wbb
(rowno varchar2(50),
operation varchar2(50),
iphone varchar2(50),
username varchar2(50),
userid varchar2(50)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY data_dir

ACCESS Parameters
(
RECORDS DELIMITED BY NEWLINE
badfile  bad_dir:'test.bad'
logfile log_dir:'test.log'
discardfile dis_dir:'test.dis'
FIELDS TERMINATED BY ','
MISSING FIELD VALUES ARE NULL
(
rowno,
operation,
iphone,
username,
userid
)
)
LOCATION('extenal.dat')
)reject limit unlimited

四.查询外部表验证数据

select * from wbb t;

120080629修改1301110022邹雪辉01110022
220080629修改1302050023王晓斌02050023
320080629修改1306060130邵静06060130
420080629修改1304020386张晋04020386
520080629修改1301070082许征01070082

今天因为要转换10万条数据到新系统中来，特意的想使用一下外部表，实践证明是可行的，以上只是一这简单的例子