外部表介绍
ORACLE外部表用来存取数据库以外的文本文件（Text File）或ORACLE专属格式文件。因此，建立外部表时不会产生段、区、数据块等存储结构，只有与表相关的定义放在数据字典中。外部表，顾名思义，存储在数据库外面的表。当存取时才能从ORACLE专属格式文件中取得数据，外部表仅供查询，不能对外部表的内容进行修改（INSERT、UPDATE、DELETE操作）。不能对外部表建立索引。因为创建索引就意味着要存在对应的索引记录。而外部表其实在没有存储在数据库中。故在外部是无法建立索引的。如果硬要建立的话，则系统会提示“操作在外部组织表上不受支持”的错误提示。
Notice: 外部表是ORACLE 9i后引入的。
External Table Restrictions
The following are restrictions on external tables:
The ANALYZE statement is not supported for gathering statistics for external tables. Use the DBMS_STATS package instead.
Virtual columns are not supported
1：创建目录对象并授权
从9i开始，ORACLE数据库若需要存取文件系统，就必须使用目录对象，以相对路径方式存取文件，强化数据库的安全性。建立目录对象、授予权限。
SQL>CREATE OR REPLACE DIRECTORY ext_test AS '/home/oracle/test';
给用户授予指定目录的操作权限
SQL>GRANT READ,WRITE ON DIRECTORY DUMP_DIR TO ETL;
2：创建外部表
[oracle@odd ~]$ cat student.data ”@#$“作为列的分隔符
10001@#$kerry@#$male@#$28@#$1
10002@#$jimmy@#$male@#$22@#$1
10003@#$ken@#$male@#$21@#$1
10004@#$merry@#$femal@#$20@#$1
CREATE TABLE EXTER_TEST
(
ID NUMBER(5) ,
NAME VARCHAR(12) ,
SEX VARCHAR(8) ,
AGE NUMBER(3) ,
GRADE NUMBER(1)
) ORGANIZATION EXTERNAL
(
type oracle_loader /定义外部表解析器，ORACLE_LOADER为默认解析器
default directory dump_dir
access parameters //解析器定义的一些访问参数
(
records delimited by newline //记录以换行作为结束符
fields terminated by '@#$'
)
location ('student.data')
);
SQL> alter table ext_test_tab reject limit unlimited; //需要解除一些限制才能够查询到表中数据
Table altered.
SQL> select * from ext_test_tab;
ID NAME SEX AGE GRADE
---------- ------------ -------- ---------- ----------
10001 kerry male 28 1
10002 jimmy male 22 1
10003 ken male 21 1
10004 merry femal 20 1
此时在指定本目录下会产生两个文件，.bad 和 .log文件。其中log记录访问外部表的记录信息，bad文件记录错误记录的信息
修改外部表
Table 20-5 ALTER TABLE Clauses for External Tables
| ALTER TABLE Clause | Description | Example |
| REJECT LIMIT | Changes the reject limit | ALTER TABLE admin_ext_employees REJECT LIMIT 100; |
| PROJECT COLUMN | Determines how the access driver validates rows in subsequent queries: PROJECT COLUMN REFERENCED: the access driver processes only the columns in the select list of the query. This setting may not provide a consistent set of rows when querying a different column list from the same external table. PROJECT COLUMN ALL: the access driver processes all of the columns defined on the external table. This setting always provides a consistent set of rows when querying an external table. This is the default. | ALTER TABLE admin_ext_employees PROJECT COLUMN REFERENCED; ALTER TABLE admin_ext_employees PROJECT COLUMN ALL; |
| DEFAULT DIRECTORY | Changes the default directory specification | ALTER TABLE admin_ext_employees DEFAULT DIRECTORY admin_dat2_dir; |
External tables can be preprocessed by user-supplied preprocessor programs. By using a preprocessing program, users can use data from a file that is not in a format supported by the driver. For example, a user may want to access data stored in a compressed format. Specifying a decompression program for the ORACLE_LOADER access driver allows the data to be decompressed as the access driver processes the data.
oracle可以利用在创建外部表命令之前执行一些任务，这项功能可以通过preprocess参数指定。
例：
CREATE TABLE sales_transactions_ext (PROD_ID NUMBER, CUST_ID NUMBER, TIME_ID DATE, CHANNEL_ID CHAR, PROMO_ID NUMBER, QUANTITY_SOLD NUMBER, AMOUNT_SOLD NUMBER(10,2), UNIT_COST NUMBER(10,2), UNIT_PRICE NUMBER(10,2)) ORGANIZATION external (TYPE oracle_loader DEFAULT DIRECTORY data_file_dir ACCESS PARAMETERS (RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII PREPROCESSOR exec_file_dir:'zcat' BADFILE log_file_dir:'sh_sales.bad_xt' LOGFILE log_file_dir:'sh_sales.log_xt' FIELDS TERMINATED BY "|" LDRTRIM ( PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID, QUANTITY_SOLD, AMOUNT_SOLD, UNIT_COST, UNIT_PRICE)) location ('sh_sales.dat.gz') )REJECT LIMIT UNLIMITED;
3:查看外部表的目录
xxx_external_locations 可以知道当前所有的目录对象以及相关的外部表，还会查询出这些外部表所对应的操作系统文件的名字。
select * from all_external_locations;
select * from user_external_locations;
select * from dba_external_locations;
4:查看外部表的详细信息
select * from user_external_tables;
select * from all_external_tables;
select * from dba_external_tables;
外部表限制：
1. 只能对表进行SELECT，不能进行DELETE、UPDATE、INSERT这些DML操作。
2. 因为外部表需要在ORACLE数据库“服务端”创建目录，OS文件必须放在这些目录中。即这些文件只能放在数据库服务端。如果数据文件不位于服务器，则无法使用外部表
3. 外部表上不能创建索引。但可以建立视图
4. 外部表不支持LOB对象。如果要使用LOB类型，则不能使用外部表。
