Oracle外部表 External Table

參考
http://www.blogjava.net/wxqxs/archive/2008/10/13/237180.html?opt=admin
http://hi.baidu.com/ray315/blog/item/153c97a33cd26caacbefd0de.html



定義
External tables access data in external sources as if it were in a table in the database.


You can connect to the database and create metadata for the external table using DDL.
The DDL for an external table consists of two parts: one part that describes the Oracle
column types, and another part (the access parameters) that describes the mapping of
the external data to the Oracle data columns.

u 建立的語法類似於： "CREATE TABLE ... ORGANIZATION EXTERNAL"
u 資料在資料庫的外部組織，是作業系統檔案。
u 作業系統檔案在資料庫中的標誌是通過一個邏輯目錄來對映的。
u 資料是隻讀的。（外部表相當於一個只讀的虛表）
u 不可以在上面執行任何 DML 操作，不可以建立索引。
u 可以查詢操作和連線。可以並行操作。


建立外部表的步驟：
1、建立以“,”分隔的檔案“TestTable.csv”至“D:\Test”
2、建立一個Directory：
create directory TestTable_diras 'D:\Test' ;
3、建立一個外部表：
create table TestTable(
ID varchar2 ( 10 ),
NAME varchar2 ( 20 ),
TYPE varchar2 ( 20 ),
AGEvarchar2 ( 20 ))
organization external (
type oracle_loader
default directory TestTable_dir
access parameters (fields terminatedby ',' )
location ( 'TestTable.csv' )
);

各類引數說明
1、type oracle_loader
資料轉換驅動器，oracle_loader為預設，也可以改換其他
2、defaultdirectory TestTable_dir
location ('TestTable.csv')
指定外部表所在資料夾以及指定檔案
3、accessparameters
設定轉換引數，例如(fields terminatedby',')表示以','為欄位間的分隔符
● 引數由訪問驅動程式定義

外部表的錯誤處理
1、REJECT LIMIT子句
在建立外部表時最後加入LIMIT子句，表示可以允許錯誤的發生個數。
* 預設的REJECT LIMIT值為0
* REJECT LIMIT UNLIMITED則不會報錯
2、BADFILE 和 NOBADFILE 子句
在accessparameters中加入BADFILE'BAD_FILE.txt'子句，則所有資料轉換錯誤的值會被放入'BAD_FILE.txt'中
使用NOBADFILE子句則表示忽略轉換錯誤的資料
● 如果不寫BADFILE或NOBADFILE，則系統自動在源目錄下生成與外部表同名的.BAD檔案
● BADFILE只能記錄前1次操作的結果，他會被第2次操作所覆蓋。
3、LOGFILE 和 NOLOGFILE 子句
在accessparameters中加入LOGFILE'LOG_FILE.log'子句，則所有Oracle的錯誤資訊放入'LOG_FILE.log'中
使用NOLOGFILE子句則表示不記錄錯誤資訊到log中
● 如果不寫LOGFILE或NOLOGFILE，則系統自動在源目錄下生成與外部表同名的.LOG檔案

修改外部表語句
外部表與堆表一樣可以之用ALTER TABLE命令修改表屬性
* REJECT LIMIT --錯誤數
* DEFAULT DIRECTORY --預設目錄
* ACCESS PARAMETERS --引數
* LOCATION --資料檔案
* ADD COLUMN --增加列
* MODIFY COLUMN --列定義
* DROP COLUMN --刪除列
* RENAME TO --外部表更名

其他約束
● 外部表無法使用insert、update、delete等操作，要修改其資料只能通過修改資料檔案。
● 外部表不能建立索引，如要建立，則需要先create table XX as select * from TestTable



PS:
1.外部表可以載入和解除安裝資料泵格式的資料，只需把organization external裡的引數type設定為oracle_datapump。
create table all_objects_unload
organization external
(
type oracle_datapump
default directory testdir
location('allobjects.dat')
)
as
select * from all_objects

轉自：http://arvinnan.iteye.com/blog/308638