Oracle之外部表

Oracle之外部表

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

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

我們假設(shè)有這樣一個(gè)文件（DATA.TXT）：1|thisisastring2|這里是個(gè)字符串3|ABC

要把這樣一個(gè)文件映射成外部表，有以下工作要做：首先，我們需要為Oracle創(chuàng)建一個(gè)Directory，

創(chuàng)建方式為，在數(shù)據(jù)庫(kù)中執(zhí)行，須用DBA用戶創(chuàng)建，并給應(yīng)用授權(quán)。createdirectoryEXT_TABLE_DIRas"/home/oracle/app/oracle/oradata/php/"

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

然后，將DATA.TXT文件拷貝到上述文件夾下；最后，創(chuàng)建一個(gè)對(duì)應(yīng)外部表，createtableEXT_TABLE_NAME(

COL_1NUMBER,

COL_2VARCHAR2(512))

organizationexternal(

typeoracle_loader

defaultdirectoryEXT_TABLE_DIR

accessparameters(fieldsterminatedby"|")location("DATA.TXT"))

rejectlimitunlimited;

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

需要補(bǔ)充的是，最后有一句“rejectlimit

unlimited”，告訴Oracle這個(gè)外部表沒(méi)有行數(shù)限制。否則，當(dāng)文件中的數(shù)據(jù)量超過(guò)200萬(wàn)行時(shí)，在對(duì)表進(jìn)行檢索時(shí)，就會(huì)出現(xiàn)ORA-30653，“rejectlimitreached”錯(cuò)誤。

Oracle9i的一項(xiàng)新特性就是External

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

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

具體的定義可以參見(jiàn)《Oracle概念手冊(cè)》，以下的幾點(diǎn)需要注意：:外部表的描述:

>創(chuàng)建的語(yǔ)法類似于："CREATETABLE...ORGANIZATIONEXTERNAL">數(shù)據(jù)在數(shù)據(jù)庫(kù)的外部組織，是操作系統(tǒng)文件。

>操作系統(tǒng)文件在數(shù)據(jù)庫(kù)中的標(biāo)志是通過(guò)一個(gè)邏輯目錄來(lái)映射的。>數(shù)據(jù)是只讀的。（外部表相當(dāng)于一個(gè)只讀的虛表）

>不可以在上面運(yùn)行任何DML操作，不可以創(chuàng)建索引。>可以查詢操作和連接�？梢圆⑿胁僮�。例子:

假如有如下兩個(gè)數(shù)據(jù)文件:1:數(shù)據(jù)文件的格式F1.TXT文件:13234,FIRSTS46464,TESTAF2.TEXT文件:13234,SECONDS46464,TEST

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

sql>createdirectorytest_diras"E:temp";

sql>grantread,writeondirectorytest_dirtousers;注意：創(chuàng)建完畢邏輯目錄之后要把平面文件拷貝到該目錄下，另外還要注意文件名字不要寫(xiě)錯(cuò)。

一定要給oracle用戶對(duì)這個(gè)目錄可讀可寫(xiě)的權(quán)限，操作系統(tǒng)層面，如使用chmod-R777test_dir；

3:使用被授權(quán)的用戶users創(chuàng)建外部表:createtabletest_table(ms_novarchar(20),tipvarchar(20),descsvarchar(20))

ORGANIZATIONEXTERNAL(

TYPEORACLE_LOADER

DEFAULTDIRECTORYtest_dirACCESSParameters(

RECORDSDELIMITEDBYNEWLINEbadfile"bad_dev.txt"LOGFILE"log_dev.txt"

FIELDSTERMINATEDBY","

MISSINGFIELDVALUESARENULL(ms_no,tip,descs))

LOCATION("F1.txt","F2.txt"));

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

4:進(jìn)行SELECT操作看是否正確;SQL>select*fromtest_table結(jié)果如下:

MS_NOTIPDESCS

------------------------------------------------------------13234FIRSTS46464TESTA13234SECONDS46464TEST

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

SQL>DESCDBA_EXTERNAL_TABLES;NameTypeNullable

-----------------------------------------OWNERVARCHAR2(30)

TABLE_NAMEVARCHAR2(30)TYPE_OWNERCHAR(3)YTYPE_NAMEVARCHAR2(30)

DEFAULT_DIRECTORY_OWNERCHAR(3)YDEFAULT_DIRECTORY_NAMEVARCHAR2(30)REJECT_LIMITVARCHAR2(40)YACCESS_TYPEVARCHAR2(7)Y

ACCESS_PARAMETERSVARCHAR2(4000)YSQL>SELECT

OWNER,TABLE_NAME,DEFAULT_DIRECTORY_NAME,ACCESS_PARAMETERSFRFROM

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

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

SQL>descDBA_EXTERNAL_LOCATIONS;得到該表結(jié)構(gòu):

NameTypeNullable

-------------------------------------OWNERVARCHAR2(30)

TABLE_NAMEVARCHAR2(30)LOCATIONVARCHAR2(4000)YDIRECTORY_OWNERCHAR(3)Y

DIRECTORY_NAMEVARCHAR2(30)Y

SQL>select*fromDBA_EXTERNAL_LOCATIONS;得到具體信息

擴(kuò)展閱讀：oracle外部表

oracle外部表

外部表的含義：

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

外部表使用限制：（來(lái)源于）需要先建立目錄對(duì)象：在創(chuàng)建外部表之前先要?jiǎng)?chuàng)建一個(gè)這個(gè)外部表要指向的文件所在目錄路徑的目錄；

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

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

另外在外部表格中，不能夠帶有標(biāo)題信息。如現(xiàn)在有一張表格，以逗號(hào)分隔。而在其第一列數(shù)據(jù)中有各個(gè)列的標(biāo)題信息。而數(shù)據(jù)庫(kù)系統(tǒng)在連接這個(gè)表的時(shí)候，會(huì)將這些標(biāo)題信息當(dāng)作普通的紀(jì)錄來(lái)對(duì)待。即會(huì)將這些信息也顯示在外部表中。為此如果這個(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ì)待。如在建立外部表的時(shí)候，最好確認(rèn)一下操作系統(tǒng)文件中是否包含標(biāo)題信息。如果有的話，需要?jiǎng)h除。否則的話，可能會(huì)出錯(cuò)。

最后需要說(shuō)明的是，當(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)等等。

在建立臨時(shí)表時(shí)的限制：

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

其次，這個(gè)外部表畢竟與內(nèi)部表不同。在創(chuàng)建外部表的時(shí)候，其實(shí)在數(shù)據(jù)庫(kù)中跟本沒(méi)有創(chuàng)建表。也就是說(shuō)，不會(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ù)。為此在表的操作與管理上，就會(huì)受到很大的限制。如在外部表上，是不能夠?yàn)楸韯?chuàng)建索引。因?yàn)閯?chuàng)建索引就意味著要存在對(duì)應(yīng)的索引記錄。而外部表其實(shí)在數(shù)據(jù)庫(kù)不會(huì)有存儲(chǔ)。故在外部中是無(wú)法建立索引的。如果硬要建立的話，則系統(tǒng)會(huì)提示“操作在外部組織表上不受支持”的錯(cuò)誤提示。同樣的道理，在數(shù)據(jù)庫(kù)中也不能夠更新外部表中的數(shù)據(jù)，如插入記錄、刪除記錄或者更新信息等等。簡(jiǎn)而言之，這個(gè)外部表對(duì)于數(shù)據(jù)庫(kù)來(lái)說(shuō)，是只讀的，不可更新。

刪除外部表或者目錄對(duì)象：當(dāng)外部表不用時(shí)，需要及時(shí)刪除外部表或者與之對(duì)應(yīng)的目錄對(duì)象。不過(guò)在刪除這些內(nèi)容時(shí)會(huì)有一些限制。這些限制主要是管理上的限制，而不是技術(shù)上的限制。也就是說(shuō)，Oracle數(shù)據(jù)庫(kù)系統(tǒng)沒(méi)有對(duì)其進(jìn)行強(qiáng)制的限制。但是如果數(shù)據(jù)庫(kù)管理員不遵守這些限制的話，可能會(huì)出現(xiàn)一些問(wèn)題。如要先刪除外部表，然后再刪除目錄對(duì)象。有時(shí)候一個(gè)目錄對(duì)象中可能會(huì)包含多個(gè)外部表。此時(shí)必須要確認(rèn)所有的外部表都不用了，都已經(jīng)刪除干凈了，然后才能夠刪除目錄對(duì)象。在創(chuàng)建外部表時(shí)，操作系統(tǒng)會(huì)判斷一下，與之對(duì)應(yīng)的目錄對(duì)象是否已經(jīng)創(chuàng)建。但是在刪除對(duì)象時(shí)，系統(tǒng)不會(huì)去判斷跟這個(gè)目錄對(duì)象關(guān)聯(lián)的外部表是否已經(jīng)全部刪除。如果目錄對(duì)象刪除了，但是還有外部表存在。此時(shí)查詢這個(gè)外部表的時(shí)候，系統(tǒng)就會(huì)提示“對(duì)象不存在”的錯(cuò)誤信息。所以這個(gè)刪除目錄對(duì)象時(shí)，數(shù)據(jù)庫(kù)系統(tǒng)缺乏一種檢查，此時(shí)只有數(shù)據(jù)庫(kù)管理員在刪除目錄對(duì)象時(shí)，先手工確認(rèn)一下這個(gè)目錄對(duì)象是否存在其他的外部表。要了解這個(gè)信息，則可以通過(guò)查詢dba_external_locations。通過(guò)查詢這張表，系統(tǒng)會(huì)反映當(dāng)前所有的目錄對(duì)象以及相關(guān)的外部表，還會(huì)查詢出這些外部表所對(duì)應(yīng)的操作系統(tǒng)文件的名字。先查詢這張表格，確定要?jiǎng)h除的對(duì)象沒(méi)有其他關(guān)聯(lián)的外部表時(shí)，再進(jìn)行刪除。否則的話，需要先確認(rèn)其他外部表的可用性。免得因?yàn)檎`刪除而導(dǎo)致外部表無(wú)法正常使用。對(duì)于操作系統(tǒng)平臺(tái)的限制：

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

外部表的實(shí)例：--創(chuàng)建目錄

createorreplacedirectorydir_bdumpas"D:\\oracle\\product\\10.2.0\\admin\\fgisdb\\bdump";--查詢目錄

select*fromdba_directories;--查詢外部表的目錄

select*fromdba_external_locations;--查詢外部表

select*fromdba_external_tables;--創(chuàng)建外部表

createtablealert_fgisdb(textvarchar2(400))organizationexternal(typeoracle_loader

defaultdirectorybdumpaccessparameters(

recordsdelimitedbynewlinenobadfilenodiscardfilenologfile)

location("alert_fgisdb.log"))

rejectlimitunlimited

--通過(guò)外部表查找數(shù)據(jù)庫(kù)的運(yùn)行信息

selectto_char(last_time,"dd-mon-yyyyhh24:mi")shutdown,to_char(start_time,"dd-mon-yyyyhh24:mi")startup,round((start_time-last_time)*24*60,2)mins_down,

round((last_time-lag(start_time)over(orderbyr)),2)days_up,

casewhen(lead(r)over(orderbyr)isnull)--lead函數(shù)用于取出后N行數(shù)據(jù)thenround((sysdate-start_time),2)enddays_still_upfrom(selectr,

to_date(last_time,"DyMonDDHH24:MI:SSYYYY")last_time,to_date(start_time,"DyMonDDHH24:MI:SSYYYY")start_timefrom(selectr,

text,

lag(text,1)over(orderbyr)start_time,--lag函數(shù)用于取出前n行數(shù)據(jù)lag(text,2)over(orderbyr)last_timefrom(

selectrownumr,text

fromalert_fgisdb

wheretextlike"____:_:_20_"

ortextlike"startingoracleinstance%"))

wheretextlike"startingoracleinstance%")

lag函數(shù)語(yǔ)法：lag（字段，n）lead函數(shù)語(yǔ)法與lag一樣。--更改拒絕限制

ALTERTABLEalert_fgisdbLIMIT100;--更改默認(rèn)目錄說(shuō)明

ALTERTABLEalert_fgisdbDIRECTORYDEFAULTDIRECTORYbdump;--修改訪問(wèn)參數(shù),如分隔符由","變?yōu)?|"

ALTERTABLEalert_fgisdbPARAMETERSACCESSPARAMETERS(FIELDSTERMINATEDBY"|");--修改文件位置:

ALTERTABLEalert_fgisdbLOCATION("TC_REG_MNGREGIONCODE.txt");

droptablealert_fgisdb;--刪除目錄

dropDIRECTORYbdump;

--查詢外部表（找出alert中含有ora的所有記錄）select*fromalert_fgisdbwheretextlike"ORA-%";

使用外部表卸載數(shù)據(jù)

--準(zhǔn)備一個(gè)簡(jiǎn)單的select語(yǔ)句向這個(gè)目錄中卸載數(shù)據(jù)createtableall_objects_unloadorganizationexternal(typeoracle_datapumpdefaultdirectorydir_dp

location("allobjects.dat"))--allobjects.dat文件在dir_dp目錄下asselect*fromall_objects

--將allobjects.dat文件拷到要加載該表的機(jī)器，使用如下語(yǔ)句抽取DDL重建這個(gè)表selectdbms_metadata.get_ddl("TABLE","ALL_OBJECTS_UNLOAD")fromdual;--抽取后的DDL語(yǔ)句如下：

CREATETABLE"GWM"."ALL_OBJECTS_UNLOAD"("OWNER"VARCHAR2(30),"OBJECT_NAME"VARCHAR2(30),"SUBOBJECT_NAME"VARCHAR2(30),"OBJECT_ID"NUMBER,

"DATA_OBJECT_ID"NUMBER,"OBJECT_TYPE"VARCHAR2(19),"CREATED"DATE,

"LAST_DDL_TIME"DATE,

"TIMESTAMP"VARCHAR2(19),"STATUS"VARCHAR2(7),

"TEMPORARY"VARCHAR2(1),"GENERATED"VARCHAR2(1),"SECONDARY"VARCHAR2(1))

ORGANIZATIONEXTERNAL(TYPEORACLE_DATAPUMP

DEFAULTDIRECTORY"DIR_DP"

LOCATION("allobjects.dat"))

--重建該表后，執(zhí)行如下語(yǔ)句就可以加載這個(gè)表的信息

insert/*+append*/intosome_tableselect*fromall_objects_unload;

友情提示：本文中關(guān)于《Oracle之外部表》給出的范例僅供您參考拓展思維使用，Oracle之外部表：該篇文章建議您自主創(chuàng)作。

來(lái)源：網(wǎng)絡(luò)整理 免責(zé)聲明：本文僅限學(xué)習(xí)分享，如產(chǎn)生版權(quán)問(wèn)題，請(qǐng)聯(lián)系我們及時(shí)刪除。

《Oracle之外部表》由互聯(lián)網(wǎng)用戶整理提供,轉(zhuǎn)載分享請(qǐng)保留原作者信息,謝謝!
鏈接地址：http://www.seogis.com/gongwen/747135.html

• 上一篇：201*社區(qū)服務(wù)站上半年工作匯報(bào)
• 下一篇：無(wú)法啟動(dòng)oracle監(jiān)聽(tīng)處理