外部表概述
外部表隻能在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' --日志檔案
--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
![ACS基本配置-權限等級管理[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)