外部表只能在Oracle 9i之后來(lái)使用。簡(jiǎn)單地說(shuō)，外部表，是指不存在于數(shù)據(jù)庫(kù)中的表。通過(guò)向Oracle提供描述外部表的元數(shù)據(jù)，我們可以把一個(gè)操作系統(tǒng)文件當(dāng)成一個(gè)只讀的數(shù)據(jù)庫(kù)表，就像這些數(shù)據(jù)存儲(chǔ)在一個(gè)普通數(shù)據(jù)庫(kù)表中一樣來(lái)進(jìn)行訪問(wèn)。外部表是對(duì)數(shù)據(jù)庫(kù)表的延伸。
位于文件系統(tǒng)之中，按一定格式分割，如文本文件或者其他類型的表可以作為外部表。
對(duì)外部表的訪問(wèn)可以通過(guò)SQL語(yǔ)句來(lái)完成，而不需要先將外部表中的數(shù)據(jù)裝載進(jìn)數(shù)據(jù)庫(kù)中。
外部數(shù)據(jù)表都是只讀的，因此在外部表不能夠執(zhí)行DML操作，也不能創(chuàng)建索引。
ANALYZE語(yǔ)句不支持采集外部表的統(tǒng)計(jì)數(shù)據(jù)，應(yīng)該使用DMBS_STATS包來(lái)采集外部表的統(tǒng)計(jì)數(shù)據(jù)。
在建立對(duì)象的時(shí)候，需要小心，Oracle數(shù)據(jù)庫(kù)系統(tǒng)不會(huì)去確認(rèn)這個(gè)目錄是否真的存在。如果在輸入這個(gè)目錄對(duì)象的時(shí)候，不小心把路徑寫(xiě)錯(cuò)了，那可能這個(gè)外 部表仍然可以正常建立，但是卻無(wú)法查詢到數(shù)據(jù)。由于建立目錄對(duì)象時(shí)，缺乏這種自我檢查的機(jī)制，為此在將路徑賦予給這個(gè)目錄對(duì)象時(shí)，需要特別的注意。另外需 要注意的是路徑的大小寫(xiě)。在Windows操作系統(tǒng)中，其路徑是不區(qū)分大小寫(xiě)的。而在Linux操作系統(tǒng)，這個(gè)路徑需要區(qū)分大小寫(xiě)。故在不同的操作系統(tǒng) 中，建立目錄對(duì)象時(shí)需要注意這個(gè)大小寫(xiě)的差異
建立外部表時(shí)，必須指定操作系統(tǒng)文件所使用的分隔符號(hào)。并且該分隔符有且只有一個(gè)。創(chuàng)建外部表時(shí)，不能含有標(biāo)題列。如果這個(gè)標(biāo)題信息與外部表的字段類型不一致（如字段內(nèi)容是number數(shù)據(jù)類型，而標(biāo)題信息則是字符型數(shù)據(jù)，則在查詢時(shí)就會(huì)出錯(cuò)）。如果數(shù)據(jù)類型恰巧一致的話，這個(gè)標(biāo)題信息Oracle數(shù)據(jù)庫(kù)也會(huì)當(dāng)作普通記錄來(lái)對(duì)待。
當(dāng)Oracle數(shù)據(jù)庫(kù)系統(tǒng)訪問(wèn)這個(gè)操作系統(tǒng)文件的時(shí)候，會(huì)在這個(gè)文件所在的目錄自動(dòng)創(chuàng)建一個(gè)日志文件。無(wú)論最后是否訪問(wèn)成功，這個(gè)日志文件都會(huì)如期建立。查看這個(gè)日志文件，可以了解數(shù)據(jù)庫(kù)訪問(wèn)外部表的頻率、是否成功訪問(wèn)等等。默認(rèn)情況下，該日志在與外部表的相同directory下產(chǎn)生。
對(duì)表中字段的名稱存在特殊字符的情況下，必須使用英文狀態(tài)的下的雙引號(hào)將該表列名稱連接起來(lái)。如采用”SalseID#”。
對(duì)于列名字中特殊符號(hào)未采用雙引號(hào)括起來(lái)時(shí)，會(huì)導(dǎo)致無(wú)法正常查詢數(shù)據(jù)。
建議不用使用特殊的列標(biāo)題字符
在創(chuàng)建外部表的時(shí)候，并沒(méi)有在數(shù)據(jù)庫(kù)中創(chuàng)建表，也不會(huì)為外部表分配任何的存儲(chǔ)空間。
創(chuàng)建外部表只是在數(shù)據(jù)字典中創(chuàng)建了外部表的元數(shù)據(jù)，以便對(duì)應(yīng)訪問(wèn)外部表中的數(shù)據(jù)，而不在數(shù)據(jù)庫(kù)中存儲(chǔ)外部表的數(shù)據(jù)。
簡(jiǎn)單地說(shuō)，數(shù)據(jù)庫(kù)存儲(chǔ)的只是與外部文件的一種對(duì)應(yīng)關(guān)系，如字段與字段的對(duì)應(yīng)關(guān)系。而沒(méi)有存儲(chǔ)實(shí)際的數(shù)據(jù)。
由于存儲(chǔ)實(shí)際數(shù)據(jù)，故無(wú)法為外部表創(chuàng)建索引，同時(shí)在數(shù)據(jù)使用DML時(shí)也不支持對(duì)外部表的插入、更新、刪除等操作。
一般情況下，先刪除外部表，然后再刪除目錄對(duì)象，如果目錄對(duì)象中有多個(gè)表，應(yīng)刪除所有表之后再刪除目錄對(duì)象。
如果在未刪除外部表的情況下，強(qiáng)制刪除了目錄，在查詢到被刪除的外部表時(shí)，將收到'對(duì)象不存在'的錯(cuò)誤信息。
查詢dba_external_locations來(lái)獲得當(dāng)前所有的目錄對(duì)象以及相關(guān)的外部表，同時(shí)會(huì)給出這些外部表所對(duì)應(yīng)的操作系統(tǒng)文件的名字。 如果只是在數(shù)據(jù)庫(kù)層面上刪除外部表，并不會(huì)自動(dòng)刪除操作系統(tǒng)上的外部表文件。
不同的操作系統(tǒng)對(duì)于外部表有不同的解釋和顯示方式
如在Linux操作系統(tǒng)中創(chuàng)建的文件是分號(hào)分隔且每行一條記錄，但該文件在Windows操作系統(tǒng)上打開(kāi)則并非如此。
建議避免不同操作系統(tǒng)以及不同字符集所帶來(lái)的影響
使用CREATE TABLE語(yǔ)句的ORGANIZATION EXTENERAL子句來(lái)創(chuàng)建外部表。外部表不分配任何盤(pán)區(qū)，因?yàn)閮H僅是在數(shù)據(jù)字典中創(chuàng)建元數(shù)據(jù)。
createtabletable_name
(col1 datatype1,col2 datatype2,col3 datatype3)
organization exteneral
(.....)
詳細(xì)語(yǔ)法可參見(jiàn)筆者的另兩篇文章
Oracle外部表ORACLE_DATAPUMP類型的創(chuàng)建語(yǔ)法詳解：http://czmmiao.iteye.com/blog/1268453
Oracle外部表ORACLE_LOADER類型的創(chuàng)建語(yǔ)法詳解：http://czmmiao.iteye.com/blog/1268157
create or replace directory data_dir as '/home/oracle/external_tb/data/'; grant read,write on directory data_dir to scott;
select * from ex_tb1; ENAME JOB SAL DNAME ------------------------- -------------------- ---- ------------------------- CLARK MANAGER 2450 ACCOUNTING KING PRESIDENT 5000 ACCOUNTING MILLER CLERK 1300 ACCOUNTING JONES MANAGER 2975 RESEARCH FORD ANALYST 3000 RESEARCH ADAMS CLERK 1100 RESEARCH SMITH CLERK 800 RESEARCH SCOTT ANALYST 3000 RESEARCH WARD SALESMAN 1250 SALES TURNER SALESMAN 1500 SALES ALLEN SALESMAN 1600 SALES JAMES CLERK 950 SALES BLAKE MANAGER 2850 SALES MARTIN SALESMAN 1250 SALES 14 rows selected.
對(duì)于使用上述方式創(chuàng)建的外部表可以將其復(fù)制到其他路徑作為外部表的原始數(shù)據(jù)來(lái)生成新的外部表，用于轉(zhuǎn)移數(shù)據(jù)。
create table in_tb1 (ename varchar2(10),job varchar2(9),sal number(7,2),dname varchar(14)) organization external (type oracle_datapump default directory data_dir location('in_tb1'));
create table tb1 as select * from in_tb1;
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 data_dir access parameters( records delimited by newline badfile 'emp_new%a_%p.bad' logfile 'emp_new%a_%p.log' fields terminated by ',' optionally enclosed by ''' lrtrim missing field values are null reject rows with all null fields ) location ('1.txt','2.txt') ) parallel reject limit unlimited;
select TABLE_NAME,TYPE_NAME,DEFAULT_DIRECTORY_NAME,REJECT_LIMIT,ACCESS_PARAMETERS from user_external_tables;
ORACLE_LOADER：定義外部表的缺省方式，只能只讀方式實(shí)現(xiàn)文本數(shù)據(jù)的裝載。
ORACLE_DATAPUMP：支持對(duì)數(shù)據(jù)的裝載與卸載，數(shù)據(jù)文件必須為二進(jìn)制dump文件?？梢詮耐獠勘硖崛?shù)據(jù)裝載到內(nèi)部表，也可以從內(nèi)部表卸載數(shù)據(jù)作為二進(jìn)制文件填充到外部表。
RECORDS關(guān)鍵字后定義如何識(shí)別數(shù)據(jù)行
DELIMITED BY 'XXX'——換行符，常用newline定義換行，并指明字符集。對(duì)于特殊的字符則需要單獨(dú)定義，如特殊符號(hào)，可以使用OX'十六位值'，例如tab(/t)的十六位是9，則DELIMITEDBY0X'09'；
cr(/r)的十六位是d，那么就是DELIMITEDBY0X'0D'。
SKIP X ——跳過(guò)X行數(shù)據(jù)，有些文件中第一行是列名，需要跳過(guò)第一行，則使用SKIP 1。
FIELDS關(guān)鍵字后定義如何識(shí)別字段，常用的如下：
FIELDS：TERMINATED BY 'x'——字段分割符。
ENCLOSED BY 'x'——字段引用符，包含在此符號(hào)內(nèi)的數(shù)據(jù)都當(dāng)成一個(gè)字段。
例如一行數(shù)據(jù)格式如：'abc','a''b,''c,'。使用參數(shù)TERMINATED BY ',' ENCLOSED BY '''后，系統(tǒng)會(huì)讀到兩個(gè)字段，第一個(gè)字段的值是abc，第二個(gè)字段值是a'b,'c,。
LRTRIM ——?jiǎng)h除首尾空白字符。
MISSING FIELD VALUES ARE NULL——某些字段空缺值都設(shè)為NULL。
對(duì)于字段長(zhǎng)度和分割符不確定且準(zhǔn)備用作外部表文件，可以使用UltraEdit、Editplus等來(lái)進(jìn)行分析測(cè)試，如果文件較大，則需要考慮將文件分割成小文件并從中提取數(shù)據(jù)進(jìn)行測(cè)試。
REJECT LIMIT UNLIMITED
在創(chuàng)建外部表時(shí)最后加入LIMIT子句，表示可以允許錯(cuò)誤的發(fā)生個(gè)數(shù)。默認(rèn)值為零。設(shè)定為UNLIMITED則錯(cuò)誤不受限制
BADFILE和NOBADFILE子句
用于指定將捕獲到的轉(zhuǎn)換錯(cuò)誤存放到哪個(gè)文件。如果指定了NOBADFILE則表示忽略轉(zhuǎn)換期間的錯(cuò)誤
如果未指定該參數(shù)，則系統(tǒng)自動(dòng)在源目錄下生成與外部表同名的.BAD文件BADFILE記錄本次操作的結(jié)果，下次將會(huì)被覆蓋 LOGFILE和NOLOGFILE子句
同樣在access parameters中加入LOGFILE 'LOG_FILE.log'子句，則所有Oracle的錯(cuò)誤信息放入'LOG_FILE.log'中
而NOLOGFILE子句則表示不記錄錯(cuò)誤信息到log中，如忽略該子句，系統(tǒng)自動(dòng)在源目錄下生成與外部表同名的.LOG文件
注意以下幾個(gè)常見(jiàn)的問(wèn)題
1.外部表經(jīng)常遇到BUFFER不足的情況，因此盡可能的增大READSIZE
2.換行符不對(duì)產(chǎn)生的問(wèn)題。在不同的操作系統(tǒng)中換行符的表示方法不一樣，碰到錯(cuò)誤日志提示如是換行符問(wèn)題，可以使用
UltraEdit打開(kāi)，直接看十六進(jìn)制
3.特定行報(bào)錯(cuò)時(shí)，查看帶有'BAD'的日志文件,其中保存了出錯(cuò)的數(shù)據(jù),用記事本打開(kāi)看看那里出錯(cuò),是否存在于外部表定義相沖突
１.SQLLDR可以指定多少提交一次，即ROWS=?, 外部表卻沒(méi)有，這對(duì)于大數(shù)據(jù)量的導(dǎo)入有些不方例。
2.sqlldr errors表示允許錯(cuò)誤的行數(shù)，外部表用REJECT LIMIT UNLIMITED，這個(gè)功能上基本相同。
3.外部表的列不能指定為not nullable,這樣就很難拒絕某列為空值的記錄。
4.外部表不能使用continueif ，如果記錄有換行的就比較難處理。
聯(lián)系客服
