Oracle之外部表

?

??? 有時候，數(shù)據(jù)庫海量數(shù)據(jù)的存儲，是一個令人頭疼的問題。別的不說，光是頻繁的執(zhí)行insert（1000次/秒）都是一場惡夢。但是，如果將數(shù)據(jù)保存到文家里，而不是數(shù)據(jù)庫中，數(shù)據(jù)序列化的開銷就小得多了。但是，對于文件的各種復(fù)雜檢索，又是一件相當(dāng)麻煩的事。

?

???

幸好，Oracle有這樣一種特性，它可以將某些特定格式的文件映射到數(shù)據(jù)庫中，形成一個“表”，稱為“外部表”。單用戶更改文件內(nèi)容時，外部表中的數(shù)據(jù)即隨之改變。同時，用戶又可以像檢索普通表一樣，以只讀的方式對外部表進(jìn)行檢索。

?

??? 我們假設(shè)有這樣一個文件（DATA.TXT）：

1|this is a string

2|這里是個字符串

3|ABC

??? 要把這樣一個文件映射成外部表，有以下工作要做：

??? 首先，我們需要為Oracle創(chuàng)建一個Directory，

??? 創(chuàng)建方式為，在數(shù)據(jù)庫中執(zhí)行，須用DBA用戶創(chuàng)建，并給應(yīng)用授權(quán)。

??? create directory EXT_TABLE_DIR as'/home/oracle/app/oracle/oradata/php/'

??? 注意“/home/oracle/app/oracle/oradata/php/”是一個存在于Oracle數(shù)據(jù)庫服務(wù)器本身上邊的實際存在的文件夾；

??? 然后，將DATA.TXT文件拷貝到上述文件夾下；

??? 最后，創(chuàng)建一個對應(yīng)外部表，

create table EXT_TABLE_NAME

(

COL_1 NUMBER,

COL_2 VARCHAR2(512)

)

organization external

(

type oracle_loader

default directory EXT_TABLE_DIR

access parameters (fields terminated by '|' )

location ('DATA.TXT')

)

reject limit unlimited;

???

注意藍(lán)色部分，EXT_TABLE_NAME是要映射成的外部表名稱，EXT_TABLE_DIR是第一步里我們創(chuàng)建的Oracle的Directory，“|”是文件里的分割符，DATA.TXT是文件名。

?

??? 需要補充的是，最后有一句“reject limit

unlimited”，告訴Oracle這個外部表沒有行數(shù)限制。否則，當(dāng)文件中的數(shù)據(jù)量超過200萬行時，在對表進(jìn)行檢索時，就會出現(xiàn)ORA-30653，“reject

limit reached”錯誤。

?

Oracle 9i 的一項新特性就是 External

Table，它就象通常的數(shù)據(jù)庫表一樣，擁有字段和數(shù)據(jù)類型約束，并且可以查詢，但是表中的數(shù)據(jù)卻不存儲在數(shù)據(jù)庫中，而是在與數(shù)據(jù)庫相關(guān)聯(lián)的普通外部文件里。當(dāng)你查詢

External Table 時，Oracle 將解析該文件并返回符合條件的數(shù)據(jù)，就象該數(shù)據(jù)存儲在數(shù)據(jù)庫表中一樣。

具體的定義可以參見《Oracle 概念手冊》，以下的幾點需要注意：

<一>:外部表的描述:

> 創(chuàng)建的語法類似于： "CREATE TABLE ... ORGANIZATION EXTERNAL"

> 數(shù)據(jù)在數(shù)據(jù)庫的外部組織，是操作系統(tǒng)文件。

> 操作系統(tǒng)文件在數(shù)據(jù)庫中的標(biāo)志是通過一個邏輯目錄來映射的。

> 數(shù)據(jù)是只讀的。（外部表相當(dāng)于一個只讀的虛表）

> 不可以在上面運行任何 DML 操作，不可以創(chuàng)建索引。

> 可以查詢操作和連接。可以并行操作。

例子:

假如有如下兩個數(shù)據(jù)文件:

1: 數(shù)據(jù)文件的格式

F1.TXT文件:

13234,FIRSTS

46464,TESTA

F2.TEXT文件:

13234,SECONDS

46464,TEST

2:創(chuàng)建目錄,并用DBA進(jìn)行授權(quán);

sql> create directory test_dir as'E:temp';

sql>grant read,write on directorytest_dir to users;

注意：創(chuàng)建完畢邏輯目錄之后要把平面文件拷貝到該目錄下，另外還要注意文件名字不要寫錯。

?????一定要給oracle用戶對這個目錄可讀可寫的權(quán)限，操作系統(tǒng)層面，如使用chmod -R 777 test_dir；

3:使用被授權(quán)的用戶users創(chuàng)建外部表:

create table test_table

(ms_no varchar(20),

tip varchar(20),

descs varchar(20))

ORGANIZATIONEXTERNAL

(

TYPEORACLE_LOADER

DEFAULTDIRECTORY test_dir

ACCESS Parameters

(

RECORDSDELIMITED BY NEWLINE

badfile 'bad_dev.txt'

LOGFILE 'log_dev.txt'

FIELDS TERMINATED BY ','

MISSING FIELD VALUES ARE NULL

(ms_no,tip,descs)

)

LOCATION('F1.txt','F2.txt')

)

;

表創(chuàng)建完成.當(dāng)然也可以導(dǎo)入一個文件

?

4:進(jìn)行SELECT 操作看是否正確;

SQL>select * from test_table

結(jié)果如下:

MS_NO TIP DESCS

-------------------- ----------------------------------------

13234 FIRSTS

46464 TESTA

13234 SECONDS

46464 TEST

?

<二>: 如何得到外部表的有關(guān)信息:

SQL> DESCDBA_EXTERNAL_TABLES;

Name TypeNullable

----------------------- ------------- -----

OWNERVARCHAR2(30)

TABLE_NAMEVARCHAR2(30)

TYPE_OWNERCHAR(3) Y

TYPE_NAME VARCHAR2(30)

DEFAULT_DIRECTORY_OWNERCHAR(3) Y

DEFAULT_DIRECTORY_NAME VARCHAR2(30)

REJECT_LIMITVARCHAR2(40) Y

ACCESS_TYPEVARCHAR2(7) Y

ACCESS_PARAMETERS VARCHAR2(4000) Y

SQL>SELECTOWNER,TABLE_NAME,DEFAULT_DIRECTORY_NAME,ACCESS_PARAMETERSFRFROM

DBA_EXTERNAL_TABLES;

可以得到外部表的相關(guān)信息;

?

<三>:如何得到外部路徑的信息:

SQL> desc DBA_EXTERNAL_LOCATIONS;

得到該表結(jié)構(gòu):

Name Type Nullable

--------------- -------------- --------

OWNERVARCHAR2(30)

TABLE_NAMEVARCHAR2(30)

LOCATIONVARCHAR2(4000) Y

DIRECTORY_OWNERCHAR(3) Y

DIRECTORY_NAME VARCHAR2(30) Y

SQL> select * fromDBA_EXTERNAL_LOCATIONS;

得到具體信息

?外部表的含義：

外部表是指不在數(shù)據(jù)庫中的表，如操作系統(tǒng)上的一個按一定格式分割的文本文件或者其他類型的表。這個外部表對于Oracle數(shù)據(jù)庫來說，就好像是一張視圖， 在數(shù)據(jù)庫中可以像視圖一樣進(jìn)行查詢等操作。這個視圖允許用戶在外部數(shù)據(jù)上運行任何的SQL語句，而不需要先將外部表中的數(shù)據(jù)裝載進(jìn)數(shù)據(jù)庫中。不過需要注意是，外部數(shù)據(jù)表都是只讀的，不能夠更改。

外部表使用限制：（來源于http://www.examda.com/oracle/zonghe/20101031/141746967.html）

??? 需要先建立目錄對象：在創(chuàng)建外部表之前先要創(chuàng)建一個這個外部表要指向的文件所在目錄路徑的目錄；

? ??對于操作系統(tǒng)文件的限制：

其實外部表簡單的說，就是跟操作系統(tǒng)上固定格式的文件或者表格的一個連接。為了Oracle數(shù)據(jù)庫系統(tǒng)能夠正確鏈接外部表，對于外部表的格式就提出 了比較嚴(yán)格的要求。如果不符合這些要求的話，數(shù)據(jù)庫系統(tǒng)就無法正確讀取外部表中的數(shù)據(jù)。如對于分隔符有比較嚴(yán)格的要求。雖然在外部文件或者表格中，可以使 用多種分隔符，如英文狀態(tài)下的逗號或者分號等等。但是有一個限制，即在同一個操作系統(tǒng)文件中只能夠使用一個分割符號，要么逗號或者分號等等。因為在建立外 部表時，必須指定操作系統(tǒng)文件所使用的分隔符號。如果有多種分隔符號的話，數(shù)據(jù)庫系統(tǒng)將無法識別。

　　另外在外部表格中，不能夠帶有標(biāo)題信息。如現(xiàn)在有一張表格，以逗號分隔。而在其第一列數(shù)據(jù)中有各個列的標(biāo)題信息。而數(shù)據(jù)庫系統(tǒng)在連接這個表的時 候，會將這些標(biāo)題信息當(dāng)作普通的紀(jì)錄來對待。即會將這些信息也顯示在外部表中。為此如果這個標(biāo)題信息與外部表的字段類型不一致（如字段內(nèi)容是number 數(shù)據(jù)類型，而標(biāo)題信息則是字符型數(shù)據(jù)，則在查詢時就會出錯）。如果數(shù)據(jù)類型恰巧一致的話，這個標(biāo)題信息Oracle數(shù)據(jù)庫也會當(dāng)作普通記錄來對待。如在建 立外部表的時候，最好確認(rèn)一下操作系統(tǒng)文件中是否包含標(biāo)題信息。如果有的話，需要刪除。否則的話，可能會出錯。

　　最后需要說明的是，當(dāng)Oracle數(shù)據(jù)庫系統(tǒng)訪問這個操作系統(tǒng)文件的時候，會在這個文件所在的目錄自動創(chuàng)建一個日志文件。無論最后是否訪問成功，這個日志文件都會如期建立。查看這個日志文件，可以了解數(shù)據(jù)庫訪問外部表的頻率、是否成功訪問等等。??

在建立臨時表時的限制： 在建立臨時表時，也會有不少的限制。如表中字段的名稱有一些特殊字符的話，那么這個表列的名稱必須使用英文狀態(tài)的下的雙引號連接起來。如采用 “studentno#”。遇到列名字中有特殊符號時，如果不采用雙引號括起來，雖然臨時表可以正常創(chuàng)建，但是在采用的時候會出現(xiàn)錯誤，無法正常查詢數(shù) 據(jù)。如數(shù)據(jù)庫系統(tǒng)可能會提醒：“數(shù)據(jù)庫插件錯誤”等信息。為此最好在創(chuàng)建臨時表時不要在列名中使用一些特殊的字符。其實不光光是建立臨時表有這種限制，建 立其他標(biāo)或者試圖都有類似的限制。

　　其次，這個外部表畢竟與內(nèi)部表不同。在創(chuàng)建外部表的時候，其實在數(shù)據(jù)庫中跟本沒有創(chuàng)建表。也就是說，不會為外部表分配任何的存儲空間。創(chuàng)建外部 表只是在數(shù)據(jù)字典中創(chuàng)建了外部表的元數(shù)據(jù)，以便對應(yīng)訪問外部表中的數(shù)據(jù)，而不在數(shù)據(jù)庫中存儲外部表的數(shù)據(jù)。簡單地說，數(shù)據(jù)庫存儲的只是與外部文件的一種對 應(yīng)關(guān)系，如字段與字段的對應(yīng)關(guān)系。而沒有存儲實際的數(shù)據(jù)。為此在表的操作與管理上，就會受到很大的限制。如在外部表上，是不能夠為表創(chuàng)建索引。因為創(chuàng)建索 引就意味著要存在對應(yīng)的索引記錄。而外部表其實在數(shù)據(jù)庫不會有存儲。故在外部中是無法建立索引的。如果硬要建立的話，則系統(tǒng)會提示“操作在外部組織表上不 受支持”的錯誤提示。同樣的道理，在數(shù)據(jù)庫中也不能夠更新外部表中的數(shù)據(jù)，如插入記錄、刪除記錄或者更新信息等等。簡而言之，這個外部表對于數(shù)據(jù)庫來說， 是只讀的，不可更新。

??? 刪除外部表或者目錄對象： 當(dāng)外部表不用時，需要及時刪除外部表或者與之對應(yīng)的目錄對象。不過在刪除這些內(nèi)容時會有一些限制。這些限制主要是管理上的限制，而不是技術(shù)上的限 制。也就是說，Oracle數(shù)據(jù)庫系統(tǒng)沒有對其進(jìn)行強制的限制。但是如果數(shù)據(jù)庫管理員不遵守這些限制的話，可能會出現(xiàn)一些問題。如要先刪除外部表，然后再 刪除目錄對象。有時候一個目錄對象中可能會包含多個外部表。此時必須要確認(rèn)所有的外部表都不用了，都已經(jīng)刪除干凈了，然后才能夠刪除目錄對象。在創(chuàng)建外部 表時，操作系統(tǒng)會判斷一下，與之對應(yīng)的目錄對象是否已經(jīng)創(chuàng)建。但是在刪除對象時，系統(tǒng)不會去判斷跟這個目錄對象關(guān)聯(lián)的外部表是否已經(jīng)全部刪除。如果目錄對 象刪除了，但是還有外部表存在。此時查詢這個外部表的時候，系統(tǒng)就會提示“對象不存在”的錯誤信息。所以這個刪除目錄對象時，數(shù)據(jù)庫系統(tǒng)缺乏一種檢查，此 時只有數(shù)據(jù)庫管理員在刪除目錄對象時，先手工確認(rèn)一下這個目錄對象是否存在其他的外部表。

　　要了解這個信息，則可以通過查詢dba_external_locations。通過查詢這張表，系統(tǒng)會反映當(dāng)前所有的目錄對象以及相關(guān)的外部 表，還會查詢出這些外部表所對應(yīng)的操作系統(tǒng)文件的名字。先查詢這張表格，確定要刪除的對象沒有其他關(guān)聯(lián)的外部表時，再進(jìn)行刪除。否則的話，需要先確認(rèn)其他 外部表的可用性。免得因為誤刪除而導(dǎo)致外部表無法正常使用。

??? 對于操作系統(tǒng)平臺的限制： 雖然Oracle數(shù)據(jù)庫是支持跨平臺的數(shù)據(jù)庫系統(tǒng)，即同時支持Windows或者Linux等多種操作系統(tǒng)。但是在使用外部表的時候需要注意一個問題，即 在兩個操作系統(tǒng)上文本文件的存儲方式是不同的。如在Windows操作系統(tǒng)上利用txt文件建立了一個以逗號作為分隔符的文件，其一行一條記錄。但是在 Linux操作系統(tǒng)上打開的話，在其就可能使在同一行中顯示了。故為了數(shù)據(jù)庫系統(tǒng)能夠正確識別操作系統(tǒng)文件，最好這個操作系統(tǒng)文件能夠和Oracle數(shù)據(jù) 庫系統(tǒng)部署在同一臺服務(wù)器上或者同一種操作系統(tǒng)上。否則的話，很可能因為格式的沖突，而導(dǎo)致數(shù)據(jù)庫系統(tǒng)無法正確讀取外部文件中的數(shù)據(jù)。

總結(jié)

以上是生活随笔為你收集整理的Oracle之外部表的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。