Oracle外部表格詳解

標籤：replace   sel   arc   服務   有一個   strong   turn   dba   訪問   

外部表格概述

外部表格只能在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資料目錄名來建立對應關係，同時授予許可權

$ mkdir -p /home/oracle/external_tb/data

create or replace directory data_dir as ‘/home/oracle/external_tb/data/‘;grant read,write on directory data_dir to scott;

b.建立外部表格

create table ex_tb1            (ename,job,sal,dname)            organization external            (type oracle_datapump default directory data_dir location(‘ex_tb1‘))            parallel 1            as select ename,job,sal,dname from emp join dept on emp.deptno=dept.deptno;

c.驗證外部表格

select * from ex_tb1;ENAME                   　　　　JOB       　　　　SAL  DNAME------------------------- -------------------- ---- -------------------------CLARK               　　　MANAGER  　　          2450 ACCOUNTINGKING                     PRESIDENT             5000 ACCOUNTINGMILLER                   CLERK                 1300 ACCOUNTINGJONES                    MANAGER               2975 RESEARCHFORD                     ANALYST               3000 RESEARCHADAMS                    CLERK                 1100 RESEARCHSMITH                    CLERK                  800 RESEARCHSCOTT                    ANALYST               3000 RESEARCHWARD                     SALESMAN              1250 SALESTURNER                   SALESMAN              1500 SALESALLEN                    SALESMAN              1600 SALESJAMES                    CLERK                  950 SALESBLAKE                    MANAGER               2850 SALESMARTIN                   SALESMAN              1250 SALES14 rows selected.

對於使用上述方式建立的外部表格可以將其複製到其他路徑作為外部表格的未經處理資料來產生新的外部表格，用於轉移資料。

d.將外部表格檔案複製一個新的檔案名稱,用以類比到其他伺服器上

$ cp /home/oracle/external_tb/data/ex_tb1 /home/oracle/external_tb/data/in_tb1

e. 建立表,將上述外部表格的資料匯入到新表中

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‘));

f.驗證新外部表格的資料

select * from in_tb1;ENAME                   　　　　JOB       　　　　SAL  DNAME------------------------- -------------------- ---- -------------------------CLARK               　　　MANAGER  　　          2450 ACCOUNTINGKING                     PRESIDENT             5000 ACCOUNTINGMILLER                   CLERK                 1300 ACCOUNTINGJONES                    MANAGER               2975 RESEARCHFORD                     ANALYST               3000 RESEARCHADAMS                    CLERK                 1100 RESEARCHSMITH                    CLERK                  800 RESEARCHSCOTT                    ANALYST               3000 RESEARCHWARD                     SALESMAN              1250 SALESTURNER                   SALESMAN              1500 SALESALLEN                    SALESMAN              1600 SALESJAMES                    CLERK                  950 SALESBLAKE                    MANAGER               2850 SALESMARTIN                   SALESMAN              1250 SALES14 rows selected.

g.建立正常的表,將外部表格資料匯入,這就是利用ORACLE_DATAPUMP類型的額外部表格實現資料移轉

create table tb1 as select * from in_tb1;

3.使用外檔案資料,使用Oracle_datapump來填充資料來產生外部表格

 

Oracle外部表格詳解