外部表
demo1.ctl内容
10,Sales,Virginia
20,Accounting,Virginia
30,Consulting,Virginia
40,Finance,Virginia

create directory ET_DIR AS 'D:TEMP';
drop table t1_et;
create table t1_et
(deptno number,dname varchar2(14),loc varchar2(13)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY et_dir
ACCESS PARAMETERS
(
records delimited by newline
fields TERMINATED BY ','
REJECT ROWS WITH ALL NULL FIELDS
)
LOCATION
(
'demo1.ctl'
)
)
REJECT LIMIT unlimited;
select * from t1_et;

增加错误数据
ABC,XYZ,Hello
select * from t1_et;
产生一个
T1_ET_2028_3696.bad
内容:
ABC,XYZ,Hello

开启优化,只选择某列,会略过其它列情况,可能返回不正确的行
未开启情况
select dname from t1_et;
DNAME
----------
Sales
Accounting
Consulting
Finance
select deptno from t1_et;
DEPTNO
-------
10
20
30
40
开启情况
alter table t1_et project column referenced;
select dname from t1_et;
DNAME
----------
Sales
Accounting
Consulting
Finance
XYZ
select deptno from t1_et;

DEPTNO
----------
10
20
30
40
加载不同文件
alter table t1_et location('demo2.ctl');
alter table t1_et location('demo1.ctl',demo2.ctl');