欧洲免费无码视频在线,亚洲日韩av中文字幕高清一区二区,亚洲人成人77777网站,韩国特黄毛片一级毛片免费,精品国产欧美,成人午夜精选视频在线观看免费,五月情天丁香宗合成人网

薈聚奇文、博采眾長、見賢思齊
當前位置:公文素材庫 > 公文素材 > 范文素材 > Oracle之外部表

Oracle之外部表

網(wǎng)站:公文素材庫 | 時間:2019-05-29 22:25:18 | 移動端:Oracle之外部表

Oracle之外部表

Oracle之外部表

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

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

我們假設有這樣一個文件(DATA.TXT):1|thisisastring2|這里是個字符串3|ABC

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

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

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

然后,將DATA.TXT文件拷貝到上述文件夾下;最后,創(chuàng)建一個對應外部表,createtableEXT_TABLE_NAME(

COL_1NUMBER,

COL_2VARCHAR2(512))

organizationexternal(

typeoracle_loader

defaultdirectoryEXT_TABLE_DIR

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

rejectlimitunlimited;

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

需要補充的是,最后有一句“rejectlimit

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

Oracle9i的一項新特性就是External

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

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

具體的定義可以參見《Oracle概念手冊》,以下的幾點需要注意::外部表的描述:

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

>操作系統(tǒng)文件在數(shù)據(jù)庫中的標志是通過一個邏輯目錄來映射的。>數(shù)據(jù)是只讀的。(外部表相當于一個只讀的虛表)

>不可以在上面運行任何DML操作,不可以創(chuàng)建索引。>可以查詢操作和連接?梢圆⑿胁僮鳌@:

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

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

sql>createdirectorytest_diras"E:temp";

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

一定要給oracle用戶對這個目錄可讀可寫的權限,操作系統(tǒng)層面,如使用chmod-R777test_dir;

3:使用被授權的用戶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)建完成.當然也可以導入一個文件

4:進行SELECT操作看是否正確;SQL>select*fromtest_table結果如下:

MS_NOTIPDESCS

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

:如何得到外部表的有關信息:

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;可以得到外部表的相關信息;

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

SQL>descDBA_EXTERNAL_LOCATIONS;得到該表結構:

NameTypeNullable

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

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

DIRECTORY_NAMEVARCHAR2(30)Y

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

擴展閱讀:oracle外部表

oracle外部表

外部表的含義:

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

外部表使用限制:(來源于)需要先建立目錄對象:在創(chuàng)建外部表之前先要創(chuàng)建一個這個外部表要指向的文件所在目錄路徑的目錄;

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

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

另外在外部表格中,不能夠帶有標題信息。如現(xiàn)在有一張表格,以逗號分隔。而在其第一列數(shù)據(jù)中有各個列的標題信息。而數(shù)據(jù)庫系統(tǒng)在連接這個表的時候,會將這些標題信息當作普通的紀錄來對待。即會將這些信息也顯示在外部表中。為此如果這個標題信息與外部表的字段類型不一致(如字段內(nèi)容是number數(shù)據(jù)類型,而標題信息則是字符型數(shù)據(jù),則在查詢時就會出錯)。如果數(shù)據(jù)類型恰巧一致的話,這個標題信息Oracle數(shù)據(jù)庫也會當作普通記錄來對待。如在建立外部表的時候,最好確認一下操作系統(tǒ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)建臨時表時不要在列名中使用一些特殊的字符。其實不光光是建立臨時表有這種限制,建立其他標或者試圖都有類似的限制。

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

刪除外部表或者目錄對象:當外部表不用時,需要及時刪除外部表或者與之對應的目錄對象。不過在刪除這些內(nèi)容時會有一些限制。這些限制主要是管理上的限制,而不是技術上的限制。也就是說,Oracle數(shù)據(jù)庫系統(tǒng)沒有對其進行強制的限制。但是如果數(shù)據(jù)庫管理員不遵守這些限制的話,可能會出現(xiàn)一些問題。如要先刪除外部表,然后再刪除目錄對象。有時候一個目錄對象中可能會包含多個外部表。此時必須要確認所有的外部表都不用了,都已經(jīng)刪除干凈了,然后才能夠刪除目錄對象。在創(chuàng)建外部表時,操作系統(tǒng)會判斷一下,與之對應的目錄對象是否已經(jīng)創(chuàng)建。但是在刪除對象時,系統(tǒng)不會去判斷跟這個目錄對象關聯(lián)的外部表是否已經(jīng)全部刪除。如果目錄對象刪除了,但是還有外部表存在。此時查詢這個外部表的時候,系統(tǒng)就會提示“對象不存在”的錯誤信息。所以這個刪除目錄對象時,數(shù)據(jù)庫系統(tǒng)缺乏一種檢查,此時只有數(shù)據(jù)庫管理員在刪除目錄對象時,先手工確認一下這個目錄對象是否存在其他的外部表。要了解這個信息,則可以通過查詢dba_external_locations。通過查詢這張表,系統(tǒng)會反映當前所有的目錄對象以及相關的外部表,還會查詢出這些外部表所對應的操作系統(tǒng)文件的名字。先查詢這張表格,確定要刪除的對象沒有其他關聯(lián)的外部表時,再進行刪除。否則的話,需要先確認其他外部表的可用性。免得因為誤刪除而導致外部表無法正常使用。對于操作系統(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)部署在同一臺服務器上或者同一種操作系統(tǒng)上。否則的話,很可能因為格式的沖突,而導致數(shù)據(jù)庫系統(tǒng)無法正確讀取外部文件中的數(shù)據(jù)。

外部表的實例:--創(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

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

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ù)語法:lag(字段,n)lead函數(shù)語法與lag一樣。--更改拒絕限制

ALTERTABLEalert_fgisdbLIMIT100;--更改默認目錄說明

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

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

ALTERTABLEalert_fgisdbLOCATION("TC_REG_MNGREGIONCODE.txt");

droptablealert_fgisdb;--刪除目錄

dropDIRECTORYbdump;

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

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

--準備一個簡單的select語句向這個目錄中卸載數(shù)據(jù)createtableall_objects_unloadorganizationexternal(typeoracle_datapumpdefaultdirectorydir_dp

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

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

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í)行如下語句就可以加載這個表的信息

insert/*+append*/intosome_tableselect*fromall_objects_unload;

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

來源:網(wǎng)絡整理 免責聲明:本文僅限學習分享,如產(chǎn)生版權問題,請聯(lián)系我們及時刪除。


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