外部表只能在Oracle 9i之後來使用。簡單地說，外部表，是指不存在於數據庫中的表。通過向Oracle提供描述外部表的元數據，我們可以把一個操作系統文件當成一個只讀的數據庫表，就像這些數據存儲在一個普通數據庫表中一樣來進行訪問。外部表是對數據庫表的延伸。
外部表的特性
位於文件系統之中，按一定格式分割，如文本文件或者其他類型的表可以作為外部表。
對外部表的訪問可以通過SQL語句來完成，而不需要先將外部表中的數據裝載進數據庫中。
外部數據表都是只讀的，因此在外部表不能夠執行DML操作，也不能創建索引。
ANALYZE語句不支持采集外部表的統計數據，應該使用DMBS_STATS包來采集外部表的統計數據。
創建外部表的注意事項
1.需要先建立目錄對象。在建立對象的時候，需要小心，Oracle數據庫系統不會去確認這個目錄是否真的存在。如果在輸入這個目錄對象的時候，不小心把路徑寫錯了，那可能這個外 部表仍然可以正常建立，但是卻無法查詢到數據。由於建立目錄對象時，缺乏這種自我檢查的機制，為此在將路徑賦予給這個目錄對象時，需要特別的注意。另外需 要注意的是路徑的大小寫。在Windows操作系統中，其路徑是不區分大小寫的。而在Linux操作系統，這個路徑需要區分大小寫。故在不同的操作系統 中，建立目錄對象時需要注意這個大小寫的差異。
2.對於操作系統文件的要求
建立外部表時，必須指定操作系統文件所使用的分隔符號。並且該分隔符有且只有一個。創建外部表時，不能含有標題列。如果這個標題信息與外部表的字段類型不一致（如字段內容是number數據類型，而標題信息則是字符型數據，則在查詢時就會出錯）。如果數據類型恰巧一致的話，這個標題信息Oracle數據庫也會當作普通記錄來對待。
當Oracle數據庫系統訪問這個操作系統文件的時候，會在這個文件所在的目錄自動創建一個日志文件。無論最後是否訪問成功，這個日志文件都會如期建立。查看這個日志文件，可以了解數據庫訪問外部表的頻率、是否成功訪問等等。默認情況下，該日志在與外部表的相同directory下產生。
3.在建立臨時表時的相關限制
對表中字段的名稱存在特殊字符的情況下，必須使用英文狀態的下的雙引號將該表列名稱連接起來。如采用”SalseID#”。
對於列名字中特殊符號未采用雙引號括起來時，會導致無法正常查詢數據。
建議不用使用特殊的列標題字符
在創建外部表的時候，並沒有在數據庫中創建表，也不會為外部表分配任何的存儲空間。
創建外部表只是在數據字典中創建了外部表的元數據，以便對應訪問外部表中的數據，而不在數據庫中存儲外部表的數據。
簡單地說，數據庫存儲的只是與外部文件的一種對應關系，如字段與字段的對應關系。而沒有存儲實際的數據。
由於存儲實際數據，故無法為外部表創建索引，同時在數據使用DML時也不支持對外部表的插入、更新、刪除等操作。
4.刪除外部表或者目錄對象
一般情況下，先刪除外部表，然後再刪除目錄對象，如果目錄對象中有多個表，應刪除所有表之後再刪除目錄對象。
如果在未刪除外部表的情況下，強制刪除了目錄，在查詢到被刪除的外部表時，將收到"對象不存在"的錯誤信息。
查詢dba_external_locations來獲得當前所有的目錄對象以及相關的外部表，同時會給出這些外部表所對應的操作系統文件的名字。 如果只是在數據庫層面上刪除外部表，並不會自動刪除操作系統上的外部表文件。
5.對於操作系統平台的限制
不同的操作系統對於外部表有不同的解釋和顯示方式
如在Linux操作系統中創建的文件是分號分隔且每行一條記錄，但該文件在Windows操作系統上打開則並非如此。
建議避免不同操作系統以及不同字符集所帶來的影響
創建外部表
使用CREATE TABLE語句的ORGANIZATION EXTENERAL子句來創建外部表。外部表不分配任何盤區，因為僅僅是在數據字典中創建元數據。
1.外部表的創建語法
createtabletable_name
(col1 datatype1,col2 datatype2,col3 datatype3)
organization exteneral
(.....)
詳細語法可參見筆者的另兩篇文章
Oracle外部表ORACLE_DATAPUMP類型的創建語法詳解：http://czmmiao.iteye.com/blog/1268453
Oracle外部表ORACLE_LOADER類型的創建語法詳解：http://czmmiao.iteye.com/blog/1268157
2.由查詢結果集，使用Oracle_datapump來填充數據來生成外部表
a.創建系統目錄以及Oracle數據目錄名來建立對應關系，同時授予權限
[oracle@oradb ~]$ mkdir-p/home/oracle/external_tb/data
SQL> create or replace directory dat_dir as '/home/oracle/external_tb/data/';
SQL> alter user scott account unlock identified by scott;
b.創建外部表
SQL>create table ex_tb1 --創建外部表
2 (ename,job,sal,dname) --表列描述，注意未指定數據類型
3 organization external
4 (
5 type oracle_datapump --使用datapump將查詢結果填充到外部表,注,此處由select生成，故不支持
oracle_loader
6 default directory dat_dir --指定外部表的存放目錄
7 location('tb1.exp，tb2.exp'))
8 parallel 2 --按並行方式來填充，這裡的並行度必須與生成的文件數量一致才能起作用，詳細算法可
9 as 以參看http://czmmiao.iteye.com/blog/1268453
10 select ename,job,sal,dname --填充使用的原始數據
11 from emp join dept
12 on emp.deptno=dept.deptno
c.驗證外部表
SQL> select * from ex_tb1;
ENAME JOB SAL DNAME
---------- --------- ---------- --------------
SMITH CLERK 800 RESEARCH
ALLEN SALESMAN 1600 SALES
..................................
MILLER CLERK 1300 ACCOUNTING
對於使用上述方式創建的外部表可以將其復制到其他路徑作為外部表的原始數據來生成新的外部表，用於轉移數據。
3.使用SQLLDR提供外部表的定義並創建外部表
關於SQL*Loader的使用請參照：SQL*Loader使用方法
我們使用SQL*Loader和下面的這個控制文件來生成外部表的定義
$ cat demo1.ctl
LOAD DATA
INFILE *
INTO TABLE DEPT_NEW
FIELDS TERMINATED BY','
(DEPTNO,DNAME,LOC)
BEGINDATA
10,Sales,Virginia
20,Accounting,Virginia
30,Consulting,Virginia
40,Finance,Virginia
賦予相應的權限和創建表
SQL>grant create any directory to scott;
SQL>grant drop any directory to scott;
SQL>create table dept_new
2 (deptno number,dname varchar2(20),loc varchar2(25));
執行sqlldr命令
$ sqlldr scott/tiger control=demo1.ctl external_table=generate_only
EXTERNAL_TABLE參數有以下三個值：
NOT_USED：默認值。
EXECUTE：這個值說明SQLLDR不會生成並執行一個SQLINSERT語句；而是會創建一個外部表，且使用一個批量SQL語句來加載。
GENERATE_ONLY：使SQLLDR並不具體加載任何數據，而只是會生成所執行的SQL DDL和DML語句，並放到它創建的日志文件中。
注：DIRECT=TRUE覆蓋EXTENAL_TABLE=GENERATE_ONLY。如果指定了DIRECT=TRUE，則會加載數據，而不會生成外部表。
$ cat demo1.log --查看sqlldr產生的日志文件
SQL*Loader: Release 10.2.0.1.0 - Production on Sun Nov 20 17:45:36 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Control File: demo1.ctl
Data File: demo1.ctl
Bad File: demo1.bad
Discard File: none specified
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation: none specified
Path used: External Table
Table DEPT_NEW, loaded from every logical record.
Insert option in effect for this table: INSERT
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
DEPTNO FIRST * , CHARACTER
DNAME NEXT * , CHARACTER
LOC NEXT * , CHARACTER
CREATE DIRECTORY statements needed for files
------------------------------------------------------------------------
CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS '/home/oracle' --創建目錄對象的語句
CREATE TABLE statement for external table:
------------------------------------------------------------------------
CREATE TABLE "SYS_SQLLDR_X_EXT_DEPT_NEW"
(
"DEPTNO" NUMBER(2),
"DNAME" VARCHAR2(14),
"LOC" VARCHAR2(13)
)
ORGANIZATION external
(
TYPE oracle_loader --指定外部表的訪問方式，9i不支持oracle_datapump
DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
ACCESS PARAMETERS --配置外部表參數
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII --記錄以換行為結束
BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'demo1.bad' --存放處理失敗的記錄文件描述
LOGFILE 'demo1.log_xt' --日志文件
READSIZE 1048576 --Oracle讀取輸入數據文件所用的默認緩沖區,此處為MB,如專用模式則從PGA分配,如共享模式
則從SGA分配
SKIP 6 --跳過的記錄數，因為我們使用了控制文件，所以前面的控制信息需要跳過
FIELDS TERMINATED BY "," LDRTRIM --描述字段的終止符
REJECT ROWS WITH ALL NULL FIELDS --所有為空值的行被跳過並且記錄到bad file.
( --下面是描述外部文件各個列的定義
"DEPTNO" CHAR(255)
TERMINATED BY ",",
"DNAME" CHAR(255)
TERMINATED BY ",",
"LOC" CHAR(255)
TERMINATED BY ","
)
)
location
(
'demo1.ctl' --描述外部文件的文件名
)
)REJECT LIMIT UNLIMITED --描述允許的錯誤數，此處為無限制
INSERT statements used to load internal tables:
------------------------------------------------------------------------
INSERT /*+ append */ INTO DEPT_NEW
(
DEPTNO,
DNAME,
LOC
)
SELECT
"DEPTNO",
"DNAME",
"LOC"
FROM "SYS_SQLLDR_X_EXT_DEPT_NEW"
statements to cleanup objects created by previous statements:
------------------------------------------------------------------------
DROP TABLE "SYS_SQLLDR_X_EXT_DEPT_NEW" --用於刪除目錄和外部表的定義信息
DROP DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
Run began on Sun Nov 20 17:45:36 2011
Run ended on Sun Nov 20 17:45:37 2011
Elapsed time was: 00:00:00.25
CPU time was: 00:00:00.05
4.使用平面文件定義並生成外部表
a.平面文件數據
1.dat：
7369,SMITH,CLERK,7902,17-DEC-80,100,0,20
7499,ALLEN,SALESMAN,7698,20-FEB-81,250,0,30
7521,WARD,SALESMAN,7698,22-FEB-81,450,0,30
7566,JONES,MANAGER,7839,02-APR-81,1150,0,20
2.dat：
7654,MARTIN,SALESMAN,7698,28-SEP-81,1250,0,30
7698,BLAKE,MANAGER,7839,01-MAY-81,1550,0,30
7934,MILLER,CLERK,7782,23-JAN-82,3500,0,10
$ pwd
/home/oracle/external_tb/data
$ ls
1.dat 2.dat dat_dir:tb_test.exp EMP_NEW_3198.log EMP_NEW_3413.log EX_TB1_3021.log
創建外部表
create table emp_new
(
emp_id number(4),
ename varchar2(15),
job varchar2(12) ,
mgr_id number(4) ,
hiredate date,
salary number(8),
comm number(8),
dept_id number(2)
)
organization external
(
type oracle_loader
default directory dat_dir
access parameters
(
records delimited by newline
fields terminated by ','
)
location
('1.dat','2.dat')
);
驗證外部表
SQL> select * from emp_new;
EMP_ID ENAME JOB MGR_ID HIREDATE SALARY COMM DEPT_ID
---------- --------------- ------------ ---------- --------- ---------- ---------- ----------
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 0 30
....................................................................
外部表不能執行DML
SQL> delete from emp_new;
delete from emp_new
*
ERROR at line 1:
ORA-30657: operation not supported on external organized table
查看外部表信息
SQL>select owner,table_name,type_name,default_directory_name,access_parameters
2 from dba_external_tables;
獲得平面文件的位置，使用如下的查詢：
SQL>select * from dba_external_locations order by table_name;
外部表定義的幾個重點
a.ORGANIZATION EXTERNAL關鍵字，必須要有。以表明定義的表為外部表。
b.重要參數外部表的類型
ORACLE_LOADER：定義外部表的缺省方式，只能只讀方式實現文本數據的裝載。
ORACLE_DATAPUMP：支持對數據的裝載與卸載，數據文件必須為二進制dump文件。可以從外部表提取數據裝載到內部表，也可以從內部表卸載數據作為二進制文件填充到外部表。
c.DEFAULT DIRECTORY：缺省的目錄指明了外部文件所在的路徑
d.LOCATION：定義了外部表的位置
f.ACCESS PARAMETERS：描述如何對外部表進行訪問
RECORDS關鍵字後定義如何識別數據行
DELIMITED BY 'XXX'——換行符，常用newline定義換行，並指明字符集。對於特殊的字符則需要單獨定義，如特殊符號，可以使用OX'十六位值'，例如tab(/t)的十六位是9，則DELIMITEDBY0X'09'；
cr(/r)的十六位是d，那麼就是DELIMITEDBY0X'0D'。
SKIP X ——跳過X行數據，有些文件中第一行是列名，需要跳過第一行，則使用SKIP 1。
FIELDS關鍵字後定義如何識別字段，常用的如下：
FIELDS：TERMINATED BY 'x'——字段分割符。
ENCLOSED BY 'x'——字段引用符，包含在此符號內的數據都當成一個字段。
例如一行數據格式如："abc","a""b,""c,"。使用參數TERMINATED BY ',' ENCLOSED BY '"'後，系統會讀到兩個字段，第一個字段的值是abc，第二個字段值是a"b,"c,。
LRTRIM ——刪除首尾空白字符。
MISSING FIELD VALUES ARE NULL——某些字段空缺值都設為NULL。
對於字段長度和分割符不確定且准備用作外部表文件，可以使用UltraEdit、Editplus等來進行分析測試，如果文件較大，則需要考慮將文件分割成小文件並從中提取數據進行測試。
外部表對錯誤的處理
REJECT LIMIT UNLIMITED
在創建外部表時最後加入LIMIT子句，表示可以允許錯誤的發生個數。默認值為零。設定為UNLIMITED則錯誤不受限制
BADFILE和NOBADFILE子句
用於指定將捕獲到的轉換錯誤存放到哪個文件。如果指定了NOBADFILE則表示忽略轉換期間的錯誤
如果未指定該參數，則系統自動在源目錄下生成與外部表同名的.BAD文件BADFILE記錄本次操作的結果，下次將會被覆蓋 LOGFILE和NOLOGFILE子句
同樣在access parameters中加入LOGFILE 'LOG_FILE.log'子句，則所有Oracle的錯誤信息放入'LOG_FILE.log'中
而NOLOGFILE子句則表示不記錄錯誤信息到log中，如忽略該子句，系統自動在源目錄下生成與外部表同名的.LOG文件
注意以下幾個常見的問題
1.外部表經常遇到BUFFER不足的情況，因此盡可能的增大READSIZE
2.換行符不對產生的問題。在不同的操作系統中換行符的表示方法不一樣，碰到錯誤日志提示如是換行符問題，可以使用
UltraEdit打開，直接看十六進制
3.特定行報錯時，查看帶有"BAD"的日志文件,其中保存了出錯的數據,用記事本打開看看那裡出錯,是否存在於外部表定義相沖突
外部表的局限性
１.SQLLDR可以指定多少提交一次，即ROWS=?, 外部表卻沒有，這對於大數據量的導入有些不方例。
2.sqlldr errors表示允許錯誤的行數，外部表用REJECT LIMIT UNLIMITED，這個功能上基本相同。
3.外部表的列不能指定為not nullable,這樣就很難拒絕某列為空值的記錄。
4.外部表不能使用continueif ，如果記錄有換行的就比較難處理。
參考至:http://space.itpub.net/22578826/viewspace-703470
http://web.njit.edu/info/oracle/DOC/server.102/b14215/et_dp_driver.htm#g1017944
http://web.njit.edu/info/oracle/DOC/server.102/b14215/et_params.htm#g1031955
http://www.examda.com/oracle/zhonghe/20090817/091840581.html
http://news.newhua.com/news1/program_database/2010/71/1071152247EKHJED8IA04B6DIA4HA3GGJ4EJ3FEE7896H215DJ8B1HI.html
本文原創，轉載請注明出處、作者
