oracle外部表的創(chuàng)建

1.創(chuàng)建兩個(gè)外部文件分別為1.txt,2.txt其中分別只有一條數(shù)據(jù)。2.創(chuàng)建一個(gè)邏輯目錄并進(jìn)行適當(dāng)授權(quán)：SQL>createdirectoryztj_diras"c:\\ztj\";目錄已創(chuàng)建

然后在物理路徑上手工創(chuàng)建實(shí)際的目錄，如：在c:\\盤(pán)下創(chuàng)建ztj目錄文件夾。SQL>grantreadondirectoryztj_dirtoscott授權(quán)成功

SQL>grantwriteondirectoryztj_dirtoscott

授權(quán)成功

注意:創(chuàng)建完畢邏輯目錄之后把平面文件拷貝到該目錄下，另外還要注意文件名字不要寫(xiě)錯(cuò)，如：把前面創(chuàng)建的外部文件1.txt,2.txt分別移動(dòng)到手工創(chuàng)建的目錄下面(c:\\ztj)3.創(chuàng)建外部表SQL>ED

編輯已寫(xiě)入的文件createtabledemo(emp_idnumber(4),enamevarchar2(12),jobvarchar2(12),mgr_idnumber(4),hiredatedate,salarynumber(8),commnumber(8),dept_idnumber(2))

organizationexternal(

typeoracle_loaderdefaultdirectoryztj_dir

accessparameters(recordsdelimitedbynewlinefieldsterminatedby",")location("1.txt","2.txt"))

SQL>/表已創(chuàng)建

4.進(jìn)行select選擇操作看看是否正確SQL>select*fromdemo;

如果要得到外部表的有關(guān)信息：SQL>selectowner,table_name,default_directory_name,access_parametersdba_external_tables;

如果DBA想要知道平面文件的位置，使用如下的查詢：SQL>select*fromDBA_EXTERNAL_LOCATIONS;

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

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

createtabledemoorganizationexternal(typeoracle_datapumpdefaultdirectoryztj_dir

location("3.txt"))--"3.txt"在c:\\ztj\\目錄下面，通過(guò)createtable創(chuàng)建demo時(shí)，"3.txt"會(huì)在c:\\ztj\\目錄下面自動(dòng)創(chuàng)建asselect*fromemp

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

CREATETABLE"SCOTT"."DEMO"(

"EMPNO"NUMBER(4,0),"ENAME"VARCHAR2(10),"JOB"VARCHAR2(9),"MGR"NUMBER(4,0),"HIREDATE"DATE,"SAL"NUMBER(7,2),"COMM"NUMBER(7,2),"DEPTNO"NUMBER(2,0)

)

ORGANIZATIONEXTERNAL(TYPEORACLE_DATAPUMP

DEFAULTDIRECTORY"ZTJ_DIR"

LOCATION("3.txt"))

抽取有用的建表語(yǔ)句建表：

CREATETABLE"SCOTT"."DEMO1"(

"EMPNO"NUMBER(4,0),"ENAME"VARCHAR2(10),"JOB"VARCHAR2(9),"MGR"NUMBER(4,0),"HIREDATE"DATE,"SAL"NUMBER(7,2),"COMM"NUMBER(7,2),"DEPTNO"NUMBER(2,0)

)

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

insert/*+append*/intodemo1select*fromdemo;與上面隔開(kāi)外部表建表語(yǔ)句

createtablealert_fgisdb(textvarchar2(400))organizationexternal(typeoracle_loader

defaultdirectorybdumpaccessparameters(

recordsdelimitedbynewlinenobadfilenodiscardfilenologfile)

location("alert_fgisdb.log"))

rejectlimitunlimited

擴(kuò)展閱讀：Oracle外部表使用

Oracle外部表

上一篇/下一篇201*-01-1023:59:06

查看(60)/評(píng)論(2)/評(píng)分(0/0)

Oracle9i的新特性之一是外部表的概念。這是在數(shù)據(jù)庫(kù)的數(shù)據(jù)字典中定義的一個(gè)表，但數(shù)據(jù)本身卻存儲(chǔ)在數(shù)據(jù)庫(kù)的外部。例如，您可定義一個(gè)外部表，它的數(shù)據(jù)來(lái)自用SQL*Loader裝載的文本文件。這樣其實(shí)正好，因?yàn)槟�需要裝載的人口普查數(shù)據(jù)就放在這樣一個(gè)文本文件中。創(chuàng)建外部表之前，需要先創(chuàng)建一個(gè)Oracle目錄對(duì)象，該Oracle目錄對(duì)象指向您的文本文件所在的操作系統(tǒng)目錄。CREATEDIRECTORYcensus_dataAS"/data/census";

現(xiàn)在，您可使用一種新形式的CREATETABLE語(yǔ)句，它看起來(lái)就像SQL語(yǔ)句和SQL*Loader控制文件的混合：

CREATETABLEcity_populations_ext(city_nameVARCHAR(9),pop_1990NUMBER,pop_201*NUMBER)

ORGANIZATIONEXTERNAL(TYPEoracle_loader

DEFAULTDIRECTORYcensus_dataACCESSPARAMETERS(RECORDSFIXED20

LOGFILEcensus_data:"city_populations.log"BADFILEcensus_data:"city_populations.bad"FIELDS

MISSINGFIELDVALUESARENULL(city_name(1:10)CHAR(9),pop_1990(11:15)INTEGEREXTERNAL(4),

POP_201*(16:20)INTEGEREXTERNAL(4)))

LOCATION("city_populations.dat"))

PARALLEL4REJECTLIMITUNLIMITED;當(dāng)創(chuàng)建一個(gè)外部表時(shí)，實(shí)際只創(chuàng)建了一些數(shù)據(jù)字典項(xiàng)。您可像對(duì)其他任何SQL表那樣對(duì)外部表進(jìn)行查詢�，F(xiàn)在，假定您有下面這張工作表：CREATETABLEcity_populations(city_nameVARCHAR(9),census_yearNUMBER,populationNUMBER,

CONSTRAINTcity_populations_pkPRIMARYKEY(city_name,census_year));

有了這張表后，您可用下列INSERT...SELECTFROM語(yǔ)句以標(biāo)準(zhǔn)化格式從外部數(shù)據(jù)文件中提取人口普查數(shù)據(jù)，并將其插入工作表。

INSERTINTOcity_populations(city_name,

census_year,population)SELECTcity_name,1990,pop_1990FROMcity_populations_extWHEREpop_1990ISNOTNULLUNIONALL

SELECTcity_name,201*,pop_201*FROMcity_populations_extWHEREpop_201*ISNOTNULL;

創(chuàng)建外部表時(shí)，由于將并行度設(shè)為4，所以數(shù)據(jù)庫(kù)將對(duì)文件進(jìn)行劃分，使其由4個(gè)并行運(yùn)行的進(jìn)程讀取。并行處理是自動(dòng)進(jìn)行的，不需要由您采取額外的操作，這確實(shí)相當(dāng)方便。要用SQL*Loader并行進(jìn)行裝載，您必須將輸入文件人工分割為多個(gè)較小的文件。

導(dǎo)入論壇收藏分享給好友推薦到圈子管理舉報(bào)TAG:":[{"c":{"ix":0,"iy":0,"iw":960,"ih":1280},"p":{"h":1280,"opacity":1,"rotate":0,"w":1080,"x":135,"x0":135,"x1":135,"x2":1215,"x3":1215,"y":113.625,"y0":1553.625,"y1":113.625,"y2":113.625,"y3":1553.625,"z":0},"ps":null,"s":{"pic_file":"/home/iknow/conv//data//bdef//9699064//9699064_3_0.jpg"},"t":"pic"}],"page":{"ph":1263.375,"pw":893.25,"iw":960,"ih":1280,"v":6,"t":"1","pptlike":false,"cx":135,"cy":113.625,"cw":758.25,"ch":1149.75}})引用刪除mget/201*-01-1100:00:49

表函數(shù)

前面展示的INSERT語(yǔ)句是通過(guò)聯(lián)合兩條SELECT語(yǔ)句而實(shí)現(xiàn)的。這意味著外部數(shù)據(jù)文件被讀了兩遍--每條SELECT語(yǔ)句都要讀一遍。但是，讀兩遍輸入文件并不合人心意，特別是在文件非常大的情況下。幸運(yùn)的是，用表函數(shù)可以解決這方面的問(wèn)題，這是我在本文中將要介紹的第二個(gè)ETL特性�？蓪⒈砗瘮�(shù)想象成一個(gè)高度簡(jiǎn)化的轉(zhuǎn)換引擎。如圖2所示，表函數(shù)將一組行作為輸入，然后返回一組不同的行作為輸出。和傳統(tǒng)函數(shù)不同的是，表函數(shù)可從SELECT語(yǔ)句的FROM子句中調(diào)用。

對(duì)于人口普查數(shù)據(jù)來(lái)說(shuō)，您打算取得每一個(gè)輸入行，并將其轉(zhuǎn)換成兩個(gè)輸出行。每個(gè)輸入行都擁有來(lái)自兩個(gè)不同的人口普查年度的數(shù)據(jù)，但是，標(biāo)準(zhǔn)目標(biāo)表要求每一年的數(shù)據(jù)占一行，所以表函數(shù)必須能將來(lái)自每個(gè)輸入行的兩次人口計(jì)數(shù)轉(zhuǎn)換成兩個(gè)單獨(dú)的輸出行。

創(chuàng)建表函數(shù)之前，您需要?jiǎng)?chuàng)建一些類型。表函數(shù)返回的總是一個(gè)記錄集，所以在最開(kāi)始的時(shí)候，請(qǐng)創(chuàng)建一個(gè)表類型，令其對(duì)應(yīng)于目標(biāo)數(shù)據(jù)表的定義。為此，首先創(chuàng)建一個(gè)對(duì)象類型來(lái)定義記錄，然后根據(jù)那個(gè)對(duì)象類型創(chuàng)建一個(gè)表類型。

CREATETYPEcity_populations_rowASOBJECT(city_nameVARCHAR2(9),census_yearNUMBER,populationNUMBER);/

CREATETYPEcity_populations_tableASTABLEOFcity_populations_row;/

函數(shù)的輸入是由對(duì)外部表city_populations_ext執(zhí)行一條SELECT語(yǔ)句而返回的數(shù)據(jù)行，因此，您需要一個(gè)恰當(dāng)?shù)腞EFCURSOR類型。下面的語(yǔ)句將創(chuàng)建一個(gè)包，其中包含一個(gè)名為pop_cursor_type的REFCURSOR類型,它與city_populations_ext表的記錄結(jié)構(gòu)相匹配。這個(gè)包還定義了一個(gè)表函數(shù)，該表函數(shù)將這樣一個(gè)游標(biāo)作為輸入?yún)��?shù)。注意您首先必須創(chuàng)建表類型city_populations_table，以便可在表函數(shù)的RETURN子句中使用那種類型：

CREATEORREPLACEPACKAGEcensus_packageAS

TYPEpop_cursor_typeISREFCURSORRETURNcity_populations_ext%ROWTYPE;FUNCTIONcensus_transform(indataINpop_cursor_type)RETURNcity_populations_table

PARALLEL_ENABLE(PARTITIONindataBYANY)PIPELINED;END;/

函數(shù)中的PARALLEL_ENABLE子句使數(shù)據(jù)庫(kù)可并行執(zhí)行函數(shù)。PARTITIONindataBYANY子句指出輸入行可被分割為任意數(shù)量的數(shù)據(jù)桶，然后便可對(duì)其進(jìn)行并行處理。PIPELINED子句使函數(shù)能遞增地返回結(jié)果集，同時(shí)其他輸入數(shù)據(jù)仍能得到處理。在調(diào)用函數(shù)的查詢執(zhí)行期間，可設(shè)想數(shù)據(jù)行都“經(jīng)過(guò)（flowingthrough）”此函數(shù)。在清單1中，在包主體（PACKAGEBODY）中定義的函數(shù)代碼負(fù)責(zé)將每個(gè)輸入行轉(zhuǎn)換成要求的兩個(gè)輸出行。

我知道這些設(shè)置起來(lái)似乎比較復(fù)雜。但它最終帶來(lái)的好處可以說(shuō)明這些麻煩是完全值得的，特別是對(duì)那些需要經(jīng)常重復(fù)的有相同規(guī)律的裝載操作來(lái)說(shuō)。通過(guò)啟用并行DML，然后使用表函數(shù)，現(xiàn)在只需一次操作，即可實(shí)現(xiàn)人口普查數(shù)據(jù)的裝載和轉(zhuǎn)換：

ALTERSESSIONENABLEPARALLELDML;

INSERT/*+APPENDPARALLEL(t,4)*/INTOcity_populationstSELECT*

FROMTABLE(census_package.census_transform(

CURSOR(SELECTcity_name,pop_1990,pop_201*

FROMcity_populations_ext)));

圖3:并行裝載和智能更新／插入

圖3:一個(gè)單步、流水線的和并行的裝載過(guò)程

人口普查數(shù)據(jù)將從外部表讀取，采用文件內(nèi)的并行機(jī)制，這一過(guò)程將被并行化處理。每個(gè)并行操作的輸出結(jié)果都會(huì)送入單獨(dú)的進(jìn)程，這些進(jìn)程也是并行運(yùn)行的，可將每個(gè)輸入行轉(zhuǎn)換成您希望的兩個(gè)輸出行。由于表函數(shù)負(fù)責(zé)進(jìn)行轉(zhuǎn)換，所以只需對(duì)外部文件遍歷一次即可。在此，還應(yīng)提醒您注意的是不必用任何形式的工作表來(lái)暫存數(shù)據(jù)，數(shù)據(jù)以流水線的方式從外部數(shù)據(jù)文件提取，經(jīng)過(guò)表函數(shù)的處理，然后直接傳送給目標(biāo)表。由于減少了數(shù)據(jù)復(fù)制的次數(shù)，所以有效地減少了對(duì)磁盤(pán)空間的占用。":[{"c":{"ix":0,"iy":0,"iw":960,"ih":1280},"p":{"h":1280,"opacity":1,"rotate":0,"w":1080,"x":135,"x0":135,"x1":135,"x2":1215,"x3":1215,"y":113.625,"y0":1553.625,"y1":113.625,"y2":113.625,"y3":1553.625,"z":0},"ps":null,"s":{"pic_file":"/home/iknow/conv//data//bdef//9699064//9699064_6_0.jpg"},"t":"pic"}],"page":{"ph":1263.375,"pw":893.25,"iw":960,"ih":1280,"v":6,"t":"1","pptlike":false,"cx":135,"cy":113.625,"cw":758.25,"ch":1149.75}})引用刪除mget/201*-01-1100:00:10

外部表性能

用SQL*Loader進(jìn)行原始數(shù)據(jù)裝載和通過(guò)一個(gè)外部表進(jìn)行相同的裝載，兩者的性能有何差異呢？這個(gè)問(wèn)題實(shí)際上是當(dāng)我聽(tīng)說(shuō)Oracle新的外部表特性時(shí)所首先想到的，而且我相信，它也是大多數(shù)人關(guān)心的問(wèn)題。我向Oracle公司的HermanBaer提出這一問(wèn)題時(shí)，他的答復(fù)是假如將一個(gè)外部表作為數(shù)據(jù)源，那么一個(gè)INSERT/*+APPEND*/語(yǔ)句采用的是同SQL＊Loader相同的內(nèi)部直接路徑插入機(jī)制。

按我的設(shè)想，在比較外部表同SQL*Loader的性能時(shí)，需要關(guān)心的并不僅僅是完成原始數(shù)據(jù)裝載所需花費(fèi)的時(shí)間，應(yīng)考察整個(gè)裝載和轉(zhuǎn)換過(guò)程的性能。使用SQL*Loader，如果在裝載期間需要使用任何類型的SQL函數(shù)，那么必須采用常規(guī)。如您所知，常規(guī)路徑裝載的性能通常遠(yuǎn)不及直接路徑裝載的性能。采用外部表時(shí)，則不會(huì)在使用SQL函數(shù)時(shí)出現(xiàn)對(duì)性能的影響。同樣，在某些情況下，使用外部表使您能避免創(chuàng)建一個(gè)中間工作表。如果正在對(duì)數(shù)據(jù)進(jìn)行歸納，則可用外部表一次完成歸納與裝載。但如果使用SQL*Loader，則必須將數(shù)據(jù)載入一個(gè)工作表，進(jìn)行歸納，再將歸納結(jié)果插入到目標(biāo)表。最后，從易用性的角度出發(fā)很重要的一點(diǎn)是，外部表使您能"透明地"并行訪問(wèn)外部數(shù)據(jù)文件。

不管怎樣，在您考察外部表的性能，或在考察任何Oracle9i的新ETL（提取、轉(zhuǎn)換和裝載）特性性能時(shí)，一定要綜合考察整個(gè)過(guò)程，而不是僅僅考察這個(gè)過(guò)程中的單獨(dú)一個(gè)步驟。

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

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

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

• 上一篇：Oracle SQL建表小結(jié)
• 下一篇：淺析Oracle監(jiān)聽(tīng)器安裝與配置