ORACLE 外部表的简单使用
外部表概述
外部表只能在Oracle 9i之后來使用。簡單地說，外部表，是指不存在于數(shù)據(jù)庫中的表。通過向Oracle提供描述外部表的元數(shù)據(jù)，
我們可以把一個操作系統(tǒng)文件當成一個只讀的數(shù)據(jù)庫表，就像這些數(shù)據(jù)存儲在一個普通數(shù)據(jù)庫表中一樣來進行訪問。外部表是對數(shù)據(jù)庫表的延伸。
外部表的特性
位于文件系統(tǒng)之中，按一定格式分割，如文本文件或者其他類型的表可以作為外部表。
對外部表的訪問可以通過SQL語句來完成，而不需要先將外部表中的數(shù)據(jù)裝載進數(shù)據(jù)庫中。
外部數(shù)據(jù)表都是只讀的，因此在外部表不能夠執(zhí)行DML操作，也不能創(chuàng)建索引。
ANALYZE語句不支持采集外部表的統(tǒng)計數(shù)據(jù)，應該使用DMBS_STATS包來采集外部表的統(tǒng)計數(shù)據(jù)。
創(chuàng)建外部表的注意事項
1.需要先建立目錄對象。
在建立對象的時候，需要小心，Oracle數(shù)據(jù)庫系統(tǒng)不會去確認這個目錄是否真的存在。如果在輸入這個目錄對象的時候，不小心把路徑寫錯了，
那可能這個外 部表仍然可以正常建立，但是卻無法查詢到數(shù)據(jù)。由于建立目錄對象時，缺乏這種自我檢查的機制，為此在將路徑賦予給這個目錄對象時，需要特別的注意。
另外需 要注意的是路徑的大小寫。在Windows操作系統(tǒng)中，其路徑是不區(qū)分大小寫的。而在Linux操作系統(tǒng)，這個路徑需要區(qū)分大小寫。故在不同的操作系統(tǒng) 中，建立目錄對象時需要注意這個大小寫的差異。
2.對于操作系統(tǒng)文件的要求
建立外部表時，必須指定操作系統(tǒng)文件所使用的分隔符號。并且該分隔符有且只有一個。創(chuàng)建外部表時，不能含有標題列。
如果這個標題信息與外部表的字段類型不一致（如字段內(nèi)容是number數(shù)據(jù)類型，而標題信息則是字符型數(shù)據(jù)，則在查詢時就會出錯）。
如果數(shù)據(jù)類型恰巧一致的話，這個標題信息Oracle數(shù)據(jù)庫也會當作普通記錄來對待。當Oracle數(shù)據(jù)庫系統(tǒng)訪問這個操作系統(tǒng)文件的時候，會在這個文件所在的目錄自動創(chuàng)建一個日志文件。無論最后是否訪問成功，這個日志文件都會如期建立。
查看這個日志文件，可以了解數(shù)據(jù)庫訪問外部表的頻率、是否成功訪問等等。默認情況下，該日志在與外部表的相同directory下產(chǎn)生。
3.在建立臨時表時的相關(guān)限制
對表中字段的名稱存在特殊字符的情況下，必須使用英文狀態(tài)的下的雙引號將該表列名稱連接起來。如采用”SalseID#”。
對于列名字中特殊符號未采用雙引號括起來時，會導致無法正常查詢數(shù)據(jù)。
建議不用使用特殊的列標題字符
在創(chuàng)建外部表的時候，并沒有在數(shù)據(jù)庫中創(chuàng)建表，也不會為外部表分配任何的存儲空間。
創(chuàng)建外部表只是在數(shù)據(jù)字典中創(chuàng)建了外部表的元數(shù)據(jù)，以便對應訪問外部表中的數(shù)據(jù)，而不在數(shù)據(jù)庫中存儲外部表的數(shù)據(jù)。
簡單地說，數(shù)據(jù)庫存儲的只是與外部文件的一種對應關(guān)系，如字段與字段的對應關(guān)系。而沒有存儲實際的數(shù)據(jù)。
由于存儲實際數(shù)據(jù)，故無法為外部表創(chuàng)建索引，同時在數(shù)據(jù)使用DML時也不支持對外部表的插入、更新、刪除等操作。
4.刪除外部表或者目錄對象
一般情況下，先刪除外部表，然后再刪除目錄對象，如果目錄對象中有多個表，應刪除所有表之后再刪除目錄對象。
如果在未刪除外部表的情況下，強制刪除了目錄，在查詢到被刪除的外部表時，將收到"對象不存在"的錯誤信息。
查詢dba_external_locations來獲得當前所有的目錄對象以及相關(guān)的外部表，同時會給出這些外部表所對應的操作系統(tǒng)文件的名字。 如果只是在數(shù)據(jù)庫層面上刪除外部表，并不會自動刪除操作系統(tǒng)上的外部表文件。
5.對于操作系統(tǒng)平臺的限制
不同的操作系統(tǒng)對于外部表有不同的解釋和顯示方式
如在Linux操作系統(tǒng)中創(chuàng)建的文件是分號分隔且每行一條記錄，但該文件在Windows操作系統(tǒng)上打開則并非如此。
建議避免不同操作系統(tǒng)以及不同字符集所帶來的影響
?
創(chuàng)建外部表：
1.創(chuàng)建目錄對象：
select * from dba_directories;
drop directory DMP3;
create directory? DMP3 as '/tmp';
2.創(chuàng)建元數(shù)據(jù)：
在系統(tǒng)/tmp 下創(chuàng)建 test.txt 文件，存儲數(shù)據(jù)
vi test.txt
3.創(chuàng)建外部表：
CREATE TABLE SMP.SN_TEMP
(
? SN? VARCHAR2(40 BYTE)
)
ORGANIZATION EXTERNAL
? (? TYPE ORACLE_LOADER
???? DEFAULT DIRECTORY DMP3
???? ACCESS PARAMETERS
?????? ( records delimited by newline
?? nobadfile
? nodiscardfile
? nologfile
??? )
???? LOCATION (DMP3:'test.txt')
? )
REJECT LIMIT UNLIMITED
NOPARALLEL
NOMONITORING;
?
5. 外部表使用：
之前外部表使用時無法和內(nèi)部表做鏈接查詢，發(fā)現(xiàn)外部表里字段竟包含了一個空格，所以使用時要截取字段，排除空格
select length(SN) from smp.SN_TEMP?
?
select substr("SN", 0,17) from smp.SN_TEMP
或者 select trim(sn) from smp.SN_TEMP
轉(zhuǎn)載于:https://www.cnblogs.com/jack-yuan/p/6509250.html
與50位技術(shù)專家面對面20年技術(shù)見證，附贈技術(shù)全景圖總結(jié)
以上是生活随笔為你收集整理的ORACLE 外部表的简单使用的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
