外表(external table)就像普通的表对像一样，可以select等，只是它是只读的，数据库中只保存了表结构的描述，表数据却没有存放在数据库内，而是存放在了文件系统上。当用户想偶然使用数据库外的结构化数据时，用起外表来就非常方便，甚至比sqlldr都要方便的多。
在这篇文章里，我们为大家演示了三步就把握Oracle外表过程。通过这次学习，也许大家就会发展原来学习oracle也是好轻易哦。
第一步：创建目录并授权
目录是数据文件的存放目标，数据文件通常要求是文本文件。这个过程在9i以前是需要配置utl_file_dir参数的。
sys@TEST!ls /home/oracle/temp
user.ctluserlist.txtuser.log
rudolf@TESTsys@TESTconn system/alibaba
Connected.sys@TEST
sys@TESTCREATE DirectorY
TEMP AS '/home/oracle/temp/';
Directory created.
sys@TESTgrant read,write on directory TEMP to rudolf;
Grant sUCceeded.
第二步：创建外表与测试
rudolf@TESTCREATE TABLE "USERLIST"
2(
3ID NUMBER,
4USERNAME VARCHAR2(30),
5EMAIL VARCHAR2(128)
6)
7ORGANIZATION external
8(
9TYPE oracle_loader
10DEFAULT DIRECTORY TEMP
11Access PARAMETERS
12(
13RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
14BADFILE 'TEMP':'userlist.bad'
15DISCARDFILE 'TEMP':'userlist.dis'
16LOGFILE 'TEMP':'user.log'
17READSIZE 1048576
18FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM
19MISSING FIELD VALUES ARE NULL
20REJECT ROWS WITH ALL NULL FIELDS
21(
22ID CHAR(30)
23TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
24USERNAME CHAR(30)
25TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
26EMAIL CHAR(128)
27TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
28)
29)
30location
31(
32'userlist.txt'
33)
34)REJECT LIMIT UNLIMITED
35rudolf@TEST/
Table created.
rudolf@TESTl
1select id,username from userlist where rownum < 10
2*rudolf@TEST/
ID USERNAME
---------- ------------------------------
1 RudolfLu
3 tomgu
6 coug
7 chao_ping
8 parrotao
9 cnoug
10 FilsDeDragon
11 Dragon
9 rows selected.
第三步：理解外表数据结构与create table ... organization external语法
大家都用过sqlldr吧？外表的数据文件的结构呢就同sqlldr能读的数据文件结构一样了。
那么语法呢？嘿嘿，别急，让我们先来做个sqlldr的练习吧：
[oracle@rac1 temp]$ head -10 userlist.txt
1,"RudolfLu"
3,"tomgu"
6,"coug"
7,"chao_ping"
8,"parrotao"
9,"cnoug"
10,"FilsDeDragon"
11,"Dragon"
15,"Xavier"
[oracle@rac1 temp]$ cat user.ctl
LOADINFILE '/home/oracle/temp/userlist.txt'
badfile '/home/oracle/temp/userlist.bad'
discardfile '/home/oracle/temp/userlist.dis'
APPENDINTO TABLE userlistfields terminated by ','
optionally enclosed by '"'
trailing nullcols
( idchar(30),
username char(30))
rudolf@TESTcreate table userlist
2(idnumber,
3 username varchar2(30)
4);
Table created.
rudolf@TEST!
[oracle@rac1 temp]$ sqlldr rudolf/nix@test2.world control=./user.ctl external_table=GENERATE_ONLY
注重，我们加了一个external_table的参数。它的作用是告诉sqlldr不用真实load数据，而是生成包含external table 创建脚本的log文件。
[oracle@rac1 temp]$ ls
user.ctl
userlist.txt
user.log[oracle@rac1 temp]$ cat user.log
SQL*Loader: Release 9.2.0.4.0 - Production on Wed Dec 10 20:50:19 2003
Copyright (c) 1982, 2002, Oracle Corporation.
All rights reserved.
Control File:
./user.ctl
Data File:
/home/oracle/temp/userlist.txt
Bad File:
/home/oracle/temp/userlist.bad
Discard File: /home/oracle/temp/userlist.dis
...CREATE DIRECTORY statements needed for files
------------------------------------------------------------------------
CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS '/home/oracle/temp/'
CREATE TABLE statement for external table:
------------------------------------------------------------------------
CREATE TABLE "SYS_SQLLDR_X_EXT_USERLIST"
(
ID NUMBER,USERNAME VARCHAR2(30))
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'userlist.bad'
DISCARDFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'userlist.dis'
LOGFILE 'user.log_xt'
READSIZE 1048576
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
(
ID CHAR(30)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
USERNAME CHAR(30)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
)
)
location
(
'userlist.txt'
)
)REJECT LIMIT UNLIMITED...
