程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> PLSQL_Oracle外部表的概念和使用（案例）（通過外部表直接查詢數據文件以節數據庫表空間），plsql_oracle數據庫

PLSQL_Oracle外部表的概念和使用（案例）（通過外部表直接查詢數據文件以節數據庫表空間），plsql_oracle數據庫

編輯：Oracle教程

PLSQL_Oracle外部表的概念和使用（案例）（通過外部表直接查詢數據文件以節數據庫表空間），plsql_oracle數據庫

2014-08-25 BaoXinjian

一、摘要

ORACLE外部表用來存取數據庫以外的文本文件（Text File）或ORACLE專屬格式文件。

因此，建立外部表時不會產生段、區、數據塊等存儲結構，只有與表相關的定義放在數據字典中。

外部表，顧名思義，存儲在數據庫外面的表。

當存取時才能從ORACLE專屬格式文件中取得數據，外部表僅供查詢，不能對外部表的內容進行修改（INSERT、UPDATE、DELETE操作）。

不能對外部表建立索引。因為創建索引就意味著要存在對應的索引記錄。而外部表其實在沒有存儲在數據庫中。

故在外部是無法建立索引的，如果硬要建立的話，則系統會提示“操作在外部組織表上不受支持”的錯誤提示。

1. 外部表特征

位於文件系統之中（一定要在數據庫服務器中，而不是其它網絡路徑），按一定格式分割，例如@#$等，文本文件或者其他類型的文件可以作為外部表。
對外部表的訪問可以通過SQL語句來完成，而不需要先將外部表中的數據裝載進數據庫中。
外部數據表都是只讀的，因此在外部表不能夠執行DML操作，也不能創建索引。
ANALYZE語句不支持采集外部表的統計數據，應該使用DMBS_STATS包來采集外部表的統計數據。
可以查詢操作和連接。也可以並行操作。
數據在數據庫的外部組織，是操作系統文件。
操作系統文件在數據庫中的標志是通過一個邏輯目錄來映射的。

2. 外部表限制

只能對表進行SELECT，不能進行DELETE、UPDATE、INSERT這些DML操作。
因為外部表需要在ORACLE數據庫“服務端”創建目錄，OS文件必須放在這些目錄中。即這些文件只能放在數據庫服務端。如果數據文件不位於服務器，則無法使用外部表。
外部表上不能創建索引。但可以建立視圖。
外部表不支持LOB對象。如果要使用LOB類型，則不能使用外部表。

3. 外部表優勢

如果要談外部表的優勢，一般會和SQLLDR來對比，外部表很多語法跟SQLLDR控制文件確實有很多類似的地方，下面談談自己的理解和"Oracel 9i&10g編程藝術"裡面的一些對比

SQLLDR需要將數據裝載入庫後才能查詢相關記錄，如果只是為了查詢一些記錄，外部表確實比SQLLDR要有優勢一些，很方便又不占用數據庫存儲空間。尤其是很大的數據，以前做移動綜合分析項目處理SGSN話單（幾百G的數據，如果全部裝載入庫，非常浪費空間和時間）時就有這樣的體會。外部表虛擬的導入過程極快
當平面文件改變時，外部表內的數據會跟著改變。這樣避免了插入、更新、刪除等操作,對於超大記錄的外部表相當有優勢，我只需要使用Shell命令就能搞定數據庫需要很高代價才能完成的事情。
外部表可以使用復雜的WHERE 條件有選擇地加載數據。盡管SQLLDR 有一個WHEN 子句用來選擇要加載的行，但是你只能使用AND 表達式和執行相等性比較的表達式，在WHEN 子句中不能使用區間（大於、小於），沒有OR 表達式，也沒有IS NULL 等。
能夠合並（MERGE）數據。可以取一個填滿數據的操作系統文件，並由它更新現有的數據庫記錄。
能執行高效的代碼查找。可以將一個外部表聯結到另一個數據庫表作為加載過程的一部分。
使用INSERT 更容易地執行多表插入。從Oracle9i 開始，通過使用復雜的WHEN 條件，可以用一個INSERT 語句插入一個或多個表。盡管SQLLDR 也可以加載到多個表中，但是相應的語法相當復雜。

二、案例 - 創建外部表，並進行增刪改查操作

案例：創建外部表，並導入測試數據，進行測試

1. 創建目錄

CREATE DIRECTORY extertable as '/home/oracle/extertable';

2. 創建測試的數據文件，並測試

3. 創建外部表

CREATE TABLE exter_test_table
(
   ID      NUMBER  (5),
   NAME    VARCHAR (12),
   SEX     VARCHAR (8),
   AGE     NUMBER  (3),
   GRADE   NUMBER  (5)
)
ORGANIZATION EXTERNAL
   ( 
     TYPE oracle_loader 
     DEFAULT DIRECTORY extertable 
     ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE FIELDS TERMINATED BY ',' ) 
     LOCATION ('student.data' )
   );

ALTER TABLE exter_test_table REJECT LIMIT UNLIMITED;

4. 創建表後，可直接查詢出在數據文件中的資料

5. 外部表只能進行select，無法進行delete / update，如需進行DML操作只能在數據文件本身中

6. 進行操作時，會在數據文件目錄中產生log文件，記錄數據導入和導出的情況

7. 查詢數據庫中外部表的情況 dba_external_locations / dba_external_tables

三、案例 - 刪除外部表

刪除外部表SQL語法跟普通表一樣，但是不同之處在於有可能還要刪除與之對應的目錄對象。

當外部表不用時，需要及時刪除外部表或者與之對應的目錄對象。

不過在刪除這些內容時會有一些限制。這些限制主要是管理上的限制，而不是技術上的限制。

也就是說，Oracle數據庫系統沒有對其進行強制的限制。

但是如果數據庫管理員不遵守這些限制的話，可能會出現一些問題。

如要先刪除外部表，然後再刪除目錄對象。有時候一個目錄對象中可能會包含多個外部表。

此時必須要確認所有的外部表都不用了，都已經刪除干淨了，然後才能夠刪除目錄對象。