外部表必须有目录对象
创建目录对象
逻辑结构，指定数据库系统的路径，一个逻辑名称 对应一操作系统物理路径。
1. 创建目录（如data）
mkdir -p u01/app/oracle/data
2. 建立目录对象
SQL> create directory d1 as '/u01/app/oracle/data';
3. 授权（默认目录对象的拥有者是sys 只有sys可以用）
SQL> select * from dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
--------- -------------------- -----------------------
SYS D1 /u01/app/oracle/data
这里拥有者都是 sys ,其它人要访问必须授权 只有sys可以读写
授予scott可以对bk1目录读写
SQL> grant read,write on directory d1 to scott;
给所有人读写权限
grant read,write on directory d1 to public ;
如需要删除这个目录
SQL> drop directory d1 ;
Directory dropped.
创建外部表
sqlldr命令自动生成外部表
例：
在数据库orcl中有一个外部表 t，在操作系统修改文件（/u01/app/oracle/data/product.csv）数据，查询表 t 立即变为最新的内容，在数据库里面 t 只能读不能修改。
1. 创建表t
SQL>create table scott.t (id number ,name char(10));
SQL> desc scott.t
Name Null? Type
------------------------------ -------- ----------
ID NUMBER
PNAME CHAR(10)
2. 外部表需要的目录对象（上文创建的/u01/app/oracle/data）
create directory d1 as '/u01/app/oracle/data';
grant read ,write on directory d1 to public;
然后把作为外部表的文件product.csv 拷贝到/u01/app/oracle/data里
product.csv 的内容
$ cat product.csv
1,a
2,b
3,c
3. 用sql*ldr自动去生成外部表语法（
借助 sqlldr自动帮生成，选择GENERATE_ONLY 只是生成日志，不做导入动作）
编辑控制文件：t.ctl
vi u01/app/oracle/data/t.ctl
LOAD DATA
INFILE '/u01/app/oracle/data/product.csv'
APPEND
INTO TABLE SCOTT.T
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(
ID INTEGER EXTERNAL,
NAME CHAR
)
通过下面语法可以创建日志文件，在日志文件里可以找到创建外部表的语法，执行如下命令，生成日志文件t.log
sqlldr scott/oracle control=t.ctl log=t.log external_table=GENERATE_ONLY
查看日志：
vi t.log
查看日志中生成外部表的语法
CREATE TABLE "SYS_SQLLDR_X_EXT_T"
(
"ID" NUMBER(38),
"NAME" CHAR(20)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET ZHS16GBK
BADFILE 'D1':'product.bad'
LOGFILE 't.log_xt'
READSIZE 1048576
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM
REJECT ROWS WITH ALL NULL FIELDS
(
"ID" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"NAME" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
)
)
location
(
'product.csv'
)
)REJECT LIMIT UNLIMITED
4. 编辑 t.sql 输入如下建外部表语句
CREATE TABLE "T"
(
"ID" NUMBER(38),
"NAME" CHAR(20)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY D1
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET ZHS16GBK
BADFILE 'D1':'t.bad'
LOGFILE 't.log_xt'
READSIZE 1048576
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM
REJECT ROWS WITH ALL NULL FIELDS
(
"ID" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
"NAME" CHAR(255)
TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
)
)
location
(
'product.csv'
)
)REJECT LIMIT UNLIMITED
/
SQL> conn scott/oracle
SQL> drop table t;
SQL> @t.sql
5. 检查表t
SQL> select * from scott.t;
ID PNAME
---------- ----------
1 aa
2 bb
3 cc
修改文本文件product.csv 的内容，你会看到表的内容一起在变
外部表只能查询，不允许更新
SQL> delete from t where id =1;
delete from t where id =1
*
ERROR at line 1:
ORA-30657: operation not supported on external organized table
