外部表介紹
ORACLE外部表用來存取數據庫以外的文本文件（Text File）或ORACLE專屬格式文件。因此，建立外部表時不會產生段、區、數據塊等存儲結構，只有與表相關的定義放在數據字典中。外部表，顧名思義，存儲在數據庫外面的表。當存取時才能從ORACLE專屬格式文件中取得數據，外部表僅供查詢，不能對外部表的內容進行修改（INSERT、UPDATE、DELETE操作）。不能對外部表建立索引。因為創建索引就意味著要存在對應的索引記錄。而外部表其實在沒有存儲在數據庫中。故在外部是無法建立索引的。如果硬要建立的話，則系統會提示“操作在外部組織表上不受支持”的錯誤提示。
Notice: 外部表是ORACLE 9i後引入的。
外部表特征
（1） 位於文件系統之中（一定要在數據庫服務器中，而不是其它網絡路徑），按一定格式分割，
例如@#$等，文本文件或者其他類型的表可以作為外部表。
（2） 對外部表的訪問可以通過SQL語句來完成，而不需要先將外部表中的數據裝載進數據庫中。
（3） 外部數據表都是只讀的，因此在外部表不能夠執行DML操作，也不能創建索引。
（4） ANALYZE語句不支持采集外部表的統計數據，應該使用DMBS_STATS包來采集外部表的統計數據。
（5） 可以查詢操作和連接。也可以並行操作。
（6） 數據在數據庫的外部組織，是操作系統文件。
（7） 操作系統文件在數據庫中的標志是通過一個邏輯目錄來映射的。
外部表范例：
1：創建目錄對象並授權
從9i開始，ORACLE數據庫若需要存取文件系統，就必須使用目錄對象，以相對路徑方式存取文件，強化數據庫的安全性。建立目錄對象、授予權限。
SQL>CREATE OR REPLACE DIRECTORY DUMP_DIR AS '/oradata/exterltab';
給用戶授予指定目錄的操作權限
SQL>GRANT READ,WRITE ON DIRECTORY DUMP_DIR TO ETL;
2：創建外部表
我創建了一個平面文件作為測試用例，如下所示，總共5條記錄，其中一條錯誤記錄
[oracle@DB-Server exterltab]$ more student.data
10001@#$kerry@#$male@#$28@#$1
10002@#$jimmy@#$male@#$22@#$1
10003@#$ken@#$male@#$21@#$1
10004@#$merry@#$femal@#$20@#$1
this is a bad file
CREATE TABLE EXTER_TEST
(
ID NUMBER(5) ,
NAME VARCHAR(12) ,
SEX VARCHAR(8) ,
AGE NUMBER(3) ,
GRADE NUMBER(1)
) ORGANIZATION EXTERNAL
(
type oracle_loader
default directory dump_dir
access parameters
(
records delimited by newline
fields terminated by '@#$'
)
location ('student.data'));
外部表語法也是蠻復雜的，參數選項非常多，這裡不做過多解釋。有興趣自然可以翻閱官方文檔。
SQL> select * from exter_test;
select * from exter_test
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-30653: reject limit reached
ORA-06512: at "SYS.ORACLE_LOADER", line 52
出現下面錯誤，是因為student.data文件中有不符合規范的記錄，可以刪除“this is a bad file”這一條記錄，但是這是為了測試下面情況，所以可以通過執行 alter table exter_test reject limit unlimited;跳過一些限制。
SQL> alter table exter_test reject limit unlimited;
Table altered.
SQL> select * from exter_test;
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
SQL>
此時去查看/oradata/exterltab,你會發現自動生成了EXTER_TEST_8907.bad 和 EXTER_TEST_8907.log兩個文件,其中log記錄訪問外部表的記錄信息，bad文件記錄錯誤記錄的信息。大體如下所示
[oracle@DB-Server exterltab]$ ls
EXTER_TEST_8907.bad EXTER_TEST_8907.log student.data
[oracle@DB-Server exterltab]$
3:查看外部表的目錄
xxx_external_locations 可以知道當前所有的目錄對象以及相關的外部表，還會查詢出這些外部表所對應的操作系統文件的名字。
select * from all_external_locations;
select * from user_external_locations;
select * from dba_external_locations;
SQL> show user
USER is "SYS"
SQL> col owner for a20
SQL> col table_name for a30
SQL> col location for a30
SQL> col directory_owner for a3;
SQL> col directory_name for a30;
SQL> select * from dba_external_locations;
OWNER TABLE_NAME LOCATION DIR DIRECTORY_NAME
--------- -------------------------- --------------- ---------------------------
SH SALES_TRANSACTIONS_EXT sale1v3.dat SYS DATA_FILE_DIR
ETL EXTER_TEST student.data SYS DUMP_DIR
4:查看外部表的詳細信息
select * from user_external_tables;
select * from all_external_tables;
select * from dba_external_tables;
SQL> desc dba_external_tables;
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
TYPE_OWNER CHAR(3)
TYPE_NAME NOT NULL VARCHAR2(30)
DEFAULT_DIRECTORY_OWNER CHAR(3)
DEFAULT_DIRECTORY_NAME NOT NULL VARCHAR2(30)
REJECT_LIMIT VARCHAR2(40)
ACCESS_TYPE VARCHAR2(7)
ACCESS_PARAMETERS VARCHAR2(4000)
PROPERTY VARCHAR2(10)
5：刪除外部表
刪除外部表SQL語法跟普通表一樣，但是不同之處在於有可能還要刪除與之對應的目錄對象。當外部表不用時，需要及時刪除外部表或者與之對應的目錄對象。不過在刪除這些內容時會有一些限制。這些限制主要是管理上的限制，而不是技術上的限制。也就是說，Oracle數據庫系統沒有對其進行強制的限制。但是如果數據庫管理員不遵守這些限制的話，可能會出現一些問題。如要先刪除外部表，然後再刪除目錄對象。有時候一個目錄對象中可能會包含多個外部表。此時必須要確認所有的外部表都不用了，都已經刪除干淨了，然後才能夠刪除目錄對象。在創建外部表時，操作系統會判斷一下，與之對應的目錄對象是否已經創建。但是在刪除對象時，系統不會去判斷跟這個目錄對象關聯的外部表是否已經全部刪除。如果目錄對象刪除了，但是還有外部表存在。此時查詢這個外部表的時候，系統就會提示“對象不存在”的錯誤信息。所以這個刪除目錄對象時，數據庫系統缺乏一種檢查，此 時只有數據庫管理員在刪除目錄對象時，先手工確認一下這個目錄對象是否存在其他的外部表。
外部表限制：
1. 只能對表進行SELECT，不能進行DELETE、UPDATE、INSERT這些DML操作。
2. 因為外部表需要在ORACLE數據庫“服務端”創建目錄，OS文件必須放在這些目錄中。即這些文件只能放在數據庫服務端。如果數據文件不位於服務器，則無法使用外部表
3. 外部表上不能創建索引。但可以建立視圖
4. 外部表不支持LOB對象。如果要使用LOB類型，則不能使用外部表。
eg：刪除外部表的記錄
SQL> delete from exter_test where id=10001;
delete from exter_test where id=10001
*
ERROR at line 1:
ORA-30657: operation not supported on external organized table
eg: 在外部表上創建視圖
SQL> create or replace view vv
2 as
3 select * from etl.exter_test;
View created.
SQL> select * from vv;
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
SQL>
外部表優勢：
如果要談外部表的優勢，一般會和SQLLDR來對比，外部表很多語法跟SQLLDR控制文件確實有很多類似的地方，下面談談自己的理解和"Oracel 9i&10g編程藝術"裡面的一些對比
1. SQLLDR需要將數據裝載入庫後才能查詢相關記錄，如果只是為了查詢一些記錄，外部表確實比SQLLDR要有優勢一些，很方便又不占用數據庫存儲空間。尤其是很大的數據，以前做移動綜合分析項目處理SGSN話單（幾百G的數據，如果全部裝載入庫，非常浪費空間和時間）時就有這樣的體會。外部表虛擬的導入過程極快
2：當平面文件改變時，外部表內的數據會跟著改變。這樣避免了插入、更新、刪除等操作,對於超大記錄的外部表相當有優勢，我只需要使用Shell命令就能搞定數據庫需要很高代價才能完成的事情。
3：外部表可以使用復雜的WHERE 條件有選擇地加載數據。盡管SQLLDR 有一個WHEN 子句用來選擇要加載的行，但是你只能使用AND 表達式和執行相等性比較的表達式，在WHEN 子句中不能使用區間（大於、小於），沒有OR 表達式，也沒有IS NULL 等。
4:能夠合並（MERGE）數據。可以取一個填滿數據的操作系統文件，並由它更新現有的數據庫記錄。
5:能執行高效的代碼查找。可以將一個外部表聯結到另一個數據庫表作為加載過程的一部分。
6:使用INSERT 更容易地執行多表插入。從Oracle9i 開始，通過使用復雜的WHEN 條件，可以用一個INSERT 語句插入一個或多個表。盡管SQLLDR 也可以加載到多個表中，但是相應的語法相當復雜。
參考資料：
http://blog.itpub.net/22578826/viewspace-703470
http://www.cnblogs.com/lanzi/archive/2010/12/28/1918755.html
http://blog.csdn.net/leshami/article/details/6078481
External Table，它就象通常的數據庫表一樣，擁有字段和數據類型約束，並且可以查詢，但是表中的數據卻不存儲在數據庫中，而是在與數據庫相關聯的普通外部文件裡。當你查詢 External Table 時，Oracle 將解析該文件並返回符合條件的數據，就象該數據存儲在數據庫表中一樣。
外部表有不規則數據，看一下目錄下的 .bad 文件
