外部表在Oracle資料庫中使用心得

外部表是指不在資料庫中的表，如作業系統上的一個按一定格式分割的文字檔案或者其他型別的表。這個外部表對於Oracle資料庫來說，就好像是一張檢視，在資料庫中可以像試圖一樣進行查詢等操作。這個試圖允許使用者在外部資料上執行任何的SQL語句，而不需要先將外部表中的資料裝載進資料庫中。不過需要注意是，外部資料表都是隻讀的，不能夠更改。不過在使用外部表時仍然有不少的限制。

　　限制一：需要先建立目錄物件。

　　在使用外部資料表的時候，需要先建立目錄物件。也就是說，要想Oracle資料庫系統（在Oracle中實現控制檔案多路複用）訪問作業系統檔案，就必須在資料庫中建立指向這個作業系統檔案的目錄物件，然後透過這個目錄物件訪問相應的作業系統檔案，即用目錄物件儲存操作檔案的儲存位置。不過需要注意，這個目錄物件普通使用者是無權建立的。一般情況下只有特權使用者或者資料庫管理員角色才可以建立目錄物件。如果允許其他使用者也可以建立目錄物件的話，就需要授權給其他使用者。

　　其實這個目錄物件就是作業系統中檔案或者表格的儲存位置。不過在建立物件的時候，需要小心，Oracle資料庫系統不會去確認這個目錄是否真的存在。如果在輸入這個目錄物件的時候，不小心把路徑寫錯了，那可能這個外部表仍然可以正常建立，但是卻無法查詢到資料。由於建立目錄物件時，缺乏這種自我檢查的機制，為此在將路徑賦予給這個目錄物件時，需要特別的注意。另外需要注意的是路徑的大小寫。在Windows作業系統中，其路徑是不區分大小寫的。而在Linux作業系統，這個路徑需要區分大小寫。故在不同的作業系統中，建立目錄物件時需要注意這個大小寫的差異。最好資料庫管理員可以養成一個習慣，無論在什麼作業系統中，都採用小寫的目錄名與檔名。那麼在不同的作業系統中，就不用受到這個大小寫的困擾了。最後需要注意的是，目錄物件名必須唯一。如果目錄物件名字有重複的話，系統會提示錯誤資訊：“名稱已有現有物件引用”，而不會自動覆蓋原有的物件名設定。

　　雖然這個物件名設定比較簡單，但是其是資料庫尋找作業系統上檔案的唯一途徑。所以這個目錄物件的建立必須確保準確。否則的話，外部表就可能無法正常使用。

　　限制二：對於作業系統檔案的限制。

　　其實外部表簡單的說，就是跟作業系統上固定格式的檔案或者表格的一個連線。為了Oracle資料庫系統能夠正確連結外部表，對於外部表的格式就提出了比較嚴格的要求。如果不符合這些要求的話，資料庫系統就無法正確讀取外部表中的資料。如對於分隔符有比較嚴格的要求。雖然在外部檔案或者表格中，可以使用多種分隔符，如英文狀態下的逗號或者分號等等。但是有一個限制，即在同一個作業系統檔案中只能夠使用一個分割符號，要麼逗號或者分號等等。因為在建立外部表時，必須指定作業系統檔案所使用的分隔符號。如果有多種分隔符號的話，資料庫系統將無法識別。

　　另外在外部表格中，不能夠帶有標題資訊。如現在有一張表格，以逗號分隔。而在其第一列資料中有各個列的標題資訊。而資料庫系統在連線這個表的時候，會將這些標題資訊當作普通的紀錄來對待。即會將這些資訊也顯示在外部表中。為此如果這個標題資訊與外部表的欄位型別不一致(如欄位內容是number資料型別，而標題資訊則是字元型資料，則在查詢時就會出錯)。如果資料型別恰巧一致的話，這個標題資訊Oracle資料庫也會當作普通記錄來對待。如在建立外部表的時候，最好確認一下作業系統檔案中是否包含標題資訊。如果有的話，需要刪除。否則的話，可能會出錯。

　　最後需要說明的是，當Oracle資料庫系統訪問這個作業系統檔案的時候，會在這個檔案所在的目錄自動建立一個日誌檔案。無論最後是否訪問成功，這個日誌檔案都會如期建立。檢視這個日誌檔案，可以瞭解資料庫訪問外部表的頻率、是否成功訪問等等。

　　限制三：在建立臨時表時的限制。

　　在建立臨時表時，也會有不少的限制。如表中欄位的名稱有一些特殊字元的話，那麼這個表列的名稱必須使用英文狀態的下的雙引號連線起來。如採用”studentno#”。遇到列名字中有特殊符號時，如果不採用雙引號括起來，雖然臨時表可以正常建立，但是在採用的時候會出現錯誤，無法正常查詢資料。如資料庫系統可能會提醒：“資料庫外掛錯誤”等資訊。為此最好在建立臨時表時不要在列名中使用一些特殊的字元。其實不光光是建立臨時表有這種限制，建立其他標或者試圖都有類似的限制
其次，這個外部表畢竟與內部表不同。在建立外部表的時候，其實在資料庫中跟本沒有建立表。也就是說，不會為外部表分配任何的儲存空間。建立外部表只是在資料字典中建立了外部表的後設資料，以便對應訪問外部表中的資料，而不在資料庫中儲存外部表的資料。簡單地說，資料庫儲存的只是與外部檔案的一種對應關係，如欄位與欄位的對應關係。而沒有儲存實際的資料。為此在表的操作與管理上，就會受到很大的限制。如在外部表上，是不能夠為表建立索引。因為建立索引就意味著要存在對應的索引記錄。而外部表其實在資料庫不會有儲存。故在外部中是無法建立索引的。如果硬要建立的話，則系統會提示“操作在外部組織表上不受支援”的錯誤提示。同樣的道理，在資料庫中也不能夠更新外部表中的資料，如插入記錄、刪除記錄或者更新資訊等等。簡而言之，這個外部表對於資料庫來說，是隻讀的，不可更新。

　　限制四：刪除外部表或者目錄物件。

　　當外部表不用時，需要及時刪除外部表或者與之對應的目錄物件。不過在刪除這些內容時會有一些限制。這些限制主要是管理上的限制，而不是技術上的限制。也就是說，Oracle資料庫系統沒有對其進行強制的限制。但是如果資料庫管理員不遵守這些限制的話，可能會出現一些問題。如要先刪除外部表，然後再刪除目錄物件。有時候一個目錄物件中可能會包含多個外部表。此時必須要確認所有的外部表都不用了，都已經刪除乾淨了，然後才能夠刪除目錄物件。在建立外部表時，作業系統會判斷一下，與之對應的目錄物件是否已經建立。但是在刪除物件時，系統不會去判斷跟這個目錄物件關聯的外部表是否已經全部刪除。如果目錄物件刪除了，但是還有外部表存在。此時查詢這個外部表的時候，系統就會提示“物件不存在”的錯誤資訊。所以這個刪除目錄物件時，資料庫系統缺乏一種檢查，此時只有資料庫管理員在刪除目錄物件時，先手工確認一下這個目錄物件是否存在其他的外部表。

　　要了解這個資訊，則可以透過查詢dba_external_locations。透過查詢這張表，系統會反映當前所有的目錄物件以及相關的外部表，還會查詢出這些外部表所對應的作業系統檔案的名字。先查詢這張表格，確定要刪除的物件沒有其他關聯的外部表時，再進行刪除。否則的話，需要先確認其他外部表的可用性。免得因為誤刪除而導致外部表無法正常使用。

　　限制五：對於作業系統平臺的限制。

　　雖然Oracle資料庫是支援跨平臺的資料庫系統，即同時支援Windows或者Linux等多種作業系統。但是在使用外部表的時候需要注意一個問題，即在兩個作業系統上文字檔案的儲存方式是不同的。如在Windows作業系統上利用txt檔案建立了一個以逗號作為分隔符的檔案，其一行一條記錄。但是在Linux作業系統上開啟的話，在其就可能使在同一行中顯示了。故為了資料庫系統能夠正確識別作業系統檔案，最好這個作業系統檔案能夠和Oracle資料庫系統部署在同一臺伺服器上或者同一種作業系統上。否則的話，很可能因為格式的衝突，而導致資料庫系統無法正確讀取外部檔案中的資料