oracle外部表的创建
1.创建两个外部文件分别为1.txt,2.txt其中分别只有一条数据。2.创建一个逻辑目录并进行适当授权：SQL>createdirectoryztj_diras"c:\\ztj\";目录已创建
然后在物理路径上手工创建实际的目录，如：在c:\\盘下创建ztj目录文件夹。SQL>grantreadondirectoryztj_dirtoscott授权成功
SQL>grantwriteondirectoryztj_dirtoscott
授权成功
注意:创建完毕逻辑目录之后把平面文件拷贝到该目录下，另外还要注意文件名字不要写错，如：把前面创建的外部文件1.txt,2.txt分别移动到手工创建的目录下面(c:\\ztj)3.创建外部表SQL>ED
编辑已写入的文件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>/表已创建
4.进行select选择操作看看是否正确SQL>select*fromdemo;
如果要得到外部表的有关信息：SQL>selectowner,table_name,default_directory_name,access_parametersdba_external_tables;
如果DBA想要知道平面文件的位置，使用如下的查询：SQL>select*fromDBA_EXTERNAL_LOCATIONS;
from使用外部表卸载数据
--准备一个简单的select语句向这个目录中卸载数据
createtabledemoorganizationexternal(typeoracle_datapumpdefaultdirectoryztj_dir
location("3.txt"))--"3.txt"在c:\\ztj\\目录下面，通过createtable创建demo时，"3.txt"会在c:\\ztj\\目录下面自动创建asselect*fromemp
讲3.txt文件拷贝到要加载该表的机器，使用如下语句抽取DDL重建这个表selectdbms_metadata.get_ddl("TABLE","DEMO")fromdual;--抽取后的DDL语句如下：
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"))
抽取有用的建表语句建表：
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)
)--重建该表后，执行如下语句就可以加载这个表的信息
insert/*+append*/intodemo1select*fromdemo;与上面隔开外部表建表语句
createtablealert_fgisdb(textvarchar2(400))organizationexternal(typeoracle_loader
defaultdirectorybdumpaccessparameters(
recordsdelimitedbynewlinenobadfilenodiscardfilenologfile)
location("alert_fgisdb.log"))
rejectlimitunlimited
扩展阅读：Oracle外部表使用
Oracle外部表
上一篇/下一篇201*-01-1023:59:06
查看(60)/评论(2)/评分(0/0)
Oracle9i的新特性之一是外部表的概念。这是在数据库的数据字典中定义的一个表，但数据本身却存储在数据库的外部。例如，您可定义一个外部表，它的数据来自用SQL*Loader装载的文本文件。这样其实正好，因为您需要装载的人口普查数据就放在这样一个文本文件中。创建外部表之前，需要先创建一个Oracle目录对象，该Oracle目录对象指向您的文本文件所在的操作系统目录。CREATEDIRECTORYcensus_dataAS"/data/census";
现在，您可使用一种新形式的CREATETABLE语句，它看起来就像SQL语句和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;当创建一个外部表时，实际只创建了一些数据字典项。您可像对其他任何SQL表那样对外部表进行查询。现在，假定您有下面这张工作表：CREATETABLEcity_populations(city_nameVARCHAR(9),census_yearNUMBER,populationNUMBER,
CONSTRAINTcity_populations_pkPRIMARYKEY(city_name,census_year));
有了这张表后，您可用下列INSERT...SELECTFROM语句以标准化格式从外部数据文件中提取人口普查数据，并将其插入工作表。
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;
创建外部表时，由于将并行度设为4，所以数据库将对文件进行划分，使其由4个并行运行的进程读取。并行处理是自动进行的，不需要由您采取额外的操作，这确实相当方便。要用SQL*Loader并行进行装载，您必须将输入文件人工分割为多个较小的文件。
导入论坛收藏分享给好友推荐到圈子管理举报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
表函数
前面展示的INSERT语句是通过联合两条SELECT语句而实现的。这意味着外部数据文件被读了两遍--每条SELECT语句都要读一遍。但是，读两遍输入文件并不合人心意，特别是在文件非常大的情况下。幸运的是，用表函数可以解决这方面的问题，这是我在本文中将要介绍的第二个ETL特性。可将表函数想象成一个高度简化的转换引擎。如图2所示，表函数将一组行作为输入，然后返回一组不同的行作为输出。和传统函数不同的是，表函数可从SELECT语句的FROM子句中调用。
对于人口普查数据来说，您打算取得每一个输入行，并将其转换成两个输出行。每个输入行都拥有来自两个不同的人口普查年度的数据，但是，标准目标表要求每一年的数据占一行，所以表函数必须能将来自每个输入行的两次人口计数转换成两个单独的输出行。
创建表函数之前，您需要创建一些类型。表函数返回的总是一个记录集，所以在最开始的时候，请创建一个表类型，令其对应于目标数据表的定义。为此，首先创建一个对象类型来定义记录，然后根据那个对象类型创建一个表类型。
CREATETYPEcity_populations_rowASOBJECT(city_nameVARCHAR2(9),census_yearNUMBER,populationNUMBER);/
CREATETYPEcity_populations_tableASTABLEOFcity_populations_row;/
函数的输入是由对外部表city_populations_ext执行一条SELECT语句而返回的数据行，因此，您需要一个恰当的REFCURSOR类型。下面的语句将创建一个包，其中包含一个名为pop_cursor_type的REFCURSOR类型,它与city_populations_ext表的记录结构相匹配。这个包还定义了一个表函数，该表函数将这样一个游标作为输入参数。注意您首先必须创建表类型city_populations_table，以便可在表函数的RETURN子句中使用那种类型：
CREATEORREPLACEPACKAGEcensus_packageAS
TYPEpop_cursor_typeISREFCURSORRETURNcity_populations_ext%ROWTYPE;FUNCTIONcensus_transform(indataINpop_cursor_type)RETURNcity_populations_table
PARALLEL_ENABLE(PARTITIONindataBYANY)PIPELINED;END;/
函数中的PARALLEL_ENABLE子句使数据库可并行执行函数。PARTITIONindataBYANY子句指出输入行可被分割为任意数量的数据桶，然后便可对其进行并行处理。PIPELINED子句使函数能递增地返回结果集，同时其他输入数据仍能得到处理。在调用函数的查询执行期间，可设想数据行都“经过（flowingthrough）”此函数。在清单1中，在包主体（PACKAGEBODY）中定义的函数代码负责将每个输入行转换成要求的两个输出行。
我知道这些设置起来似乎比较复杂。但它最终带来的好处可以说明这些麻烦是完全值得的，特别是对那些需要经常重复的有相同规律的装载操作来说。通过启用并行DML，然后使用表函数，现在只需一次操作，即可实现人口普查数据的装载和转换：
ALTERSESSIONENABLEPARALLELDML;
INSERT/*+APPENDPARALLEL(t,4)*/INTOcity_populationstSELECT*
FROMTABLE(census_package.census_transform(
CURSOR(SELECTcity_name,pop_1990,pop_201*
FROMcity_populations_ext)));
图3:并行装载和智能更新／插入
图3:一个单步、流水线的和并行的装载过程
人口普查数据将从外部表读取，采用文件内的并行机制，这一过程将被并行化处理。每个并行操作的输出结果都会送入单独的进程，这些进程也是并行运行的，可将每个输入行转换成您希望的两个输出行。由于表函数负责进行转换，所以只需对外部文件遍历一次即可。在此，还应提醒您注意的是不必用任何形式的工作表来暂存数据，数据以流水线的方式从外部数据文件提取，经过表函数的处理，然后直接传送给目标表。由于减少了数据复制的次数，所以有效地减少了对磁盘空间的占用。":[{"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进行原始数据装载和通过一个外部表进行相同的装载，两者的性能有何差异呢？这个问题实际上是当我听说Oracle新的外部表特性时所首先想到的，而且我相信，它也是大多数人关心的问题。我向Oracle公司的HermanBaer提出这一问题时，他的答复是假如将一个外部表作为数据源，那么一个INSERT/*+APPEND*/语句采用的是同SQL＊Loader相同的内部直接路径插入机制。
按我的设想，在比较外部表同SQL*Loader的性能时，需要关心的并不仅仅是完成原始数据装载所需花费的时间，应考察整个装载和转换过程的性能。使用SQL*Loader，如果在装载期间需要使用任何类型的SQL函数，那么必须采用常规。如您所知，常规路径装载的性能通常远不及直接路径装载的性能。采用外部表时，则不会在使用SQL函数时出现对性能的影响。同样，在某些情况下，使用外部表使您能避免创建一个中间工作表。如果正在对数据进行归纳，则可用外部表一次完成归纳与装载。但如果使用SQL*Loader，则必须将数据载入一个工作表，进行归纳，再将归纳结果插入到目标表。最后，从易用性的角度出发很重要的一点是，外部表使您能"透明地"并行访问外部数据文件。
不管怎样，在您考察外部表的性能，或在考察任何Oracle9i的新ETL（提取、转换和装载）特性性能时，一定要综合考察整个过程，而不是仅仅考察这个过程中的单独一个步骤。
友情提示：本文中关于《oracle外部表的创建》给出的范例仅供您参考拓展思维使用，oracle外部表的创建：该篇文章建议您自主创作。
来源：网络整理 免责声明：本文仅限学习分享，如产生版权问题，请联系我们及时删除。
公文素材库友情提示：本网站所有内容为共享上传提供，不涉及任何商业利益，本站对此不承担任何保证责任！
Copyright © www.bsmz.net Corporation, All Rights Reserved 共享时代 共享你我他 版权所有