网络管理登录入口,网站优化有前途吗,只卖域名的网站,搜索企业的软件三步教会你掌握oracle外表(external table)外表(externaltable)就像普通的表对像一样#xff0c;可以select等#xff0c;只是它是只读的#xff0c;数据库中只保存了表结构的描述#xff0c;表数据却没有存放在数据库内#xff0c;而是存放在了文件系统上。当用户想偶尔使…三步教会你掌握oracle外表(external table)外表(externaltable)就像普通的表对像一样可以select等只是它是只读的数据库中只保存了表结构的描述表数据却没有存放在数据库内而是存放在了文件系统上。当用户想偶尔使用数据库外的结构化数据时用起外表来就非常方便甚至比sqlldr都要方便的多。在这篇文章里我们为大家演示了三步就掌握oracle外表过程。通过这次学习也许大家就会发展原来学习oracle也是好容易哦。第一步创建目录并授权。目录是数据文件的存放目标数据文件通常要求是文本文件。这个过程在9i以前是需要配置utl_file_dir参数的。sysTEST!ls /home/oracle/tempuser.ctl userlist.txt user.logrudolfTESTsysTESTconn system/alibabaConnected.sysTESTsysTESTCREATE DIRECTORY TEMP AS /home/oracle/temp/;Directory created.sysTESTgrant read,write on directory TEMP to rudolf;Grant succeeded.第二步创建外表与测试rudolfTESTCREATE TABLE USERLIST2 (3 ID NUMBER,4 USERNAME VARCHAR2(30),5 EMAIL VARCHAR2(128)6 )7 ORGANIZATION external8 (9 TYPE oracle_loader10 DEFAULT DIRECTORY TEMP11 ACCESS PARAMETERS12 (13 RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII14 BADFILE TEMP:userlist.bad15 DISCARDFILE TEMP:userlist.dis16 LOGFILE TEMP:user.log17 READSIZE 104857618 FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY LDRTRIM19 MISSING FIELD VALUES ARE NULL20 REJECT ROWS WITH ALL NULL FIELDS21 (22 ID CHAR(30)23 TERMINATED BY , OPTIONALLY ENCLOSED BY ,24 USERNAME CHAR(30)25 TERMINATED BY , OPTIONALLY ENCLOSED BY ,26 EMAIL CHAR(128)27 TERMINATED BY , OPTIONALLY ENCLOSED BY 28 )29 )30 location31 (32 userlist.txt33 )34 )REJECT LIMIT UNLIMITED35rudolfTEST/Table created.rudolfTESTl1 select id,username from userlist where rownum 102*rudolfTEST/ID USERNAME---------- ------------------------------1 RudolfLu3 tomgu6 coug7 chao_ping8 parrotao9 cnoug10 FilsDeDragon11 Dragon9 rows selected.瞧成功了。外表就这么简单。可是只有二步啊第三步在哪里呢你也许会问。还有啊userlist.txt要固定的格式吗create table...的语法这样的狂复杂每一项都是什么含义呢第三步理解外表数据结构与create table ... organizationexternal语法。大家都用过sqlldr吧外表的数据文件的结构呢就同sqlldr能读的数据文件结构一样了。那么语法呢嘿嘿别急让我们先来做个sqlldr的练习吧[oraclerac1 temp]$ head -10 userlist.txt1,RudolfLu3,tomgu6,coug7,chao_ping8,parrotao9,cnoug10,FilsDeDragon11,Dragon15,Xavier[oraclerac1 temp]$ cat user.ctlLOADINFILE /home/oracle/temp/userlist.txtbadfile /home/oracle/temp/userlist.baddiscardfile /home/oracle/temp/userlist.disAPPENDINTO TABLE userlistfields terminated by , optionally enclosed by trailing nullcols( id char(30),username char(30))rudolfTESTcreate table userlist2 (id number,3 username varchar2(30)4 );Table created.rudolfTEST![oraclerac1 temp]$ sqlldr rudolf/nixtest2.world control./user.ctl external_tableGENERATE_ONLY注意我们加了一个external_table的参数。它的作用是告诉sqlldr不用真实load数据而是生成包含external table 创建脚本的log文件。[oraclerac1 temp]$ lsuser.ctl userlist.txt user.log[oraclerac1 temp]$ cat user.logSQL*Loader: Release 9.2.0.4.0 - Production on Wed Dec 10 20:50:19 2003Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.Control File: ./user.ctlData File: /home/oracle/temp/userlist.txtBad File: /home/oracle/temp/userlist.badDiscard File: /home/oracle/temp/userlist.dis...CREATE DIRECTORY statements needed for files------------------------------------CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS /home/oracle/temp/CREATE TABLE statement for external table:------------------------------------CREATE TABLE SYS_SQLLDR_X_EXT_USERLIST(ID NUMBER,USERNAME VARCHAR2(30))ORGANIZATION external(TYPE oracle_loaderDEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000ACCESS PARAMETERS(RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCIIBADFILE SYS_SQLLDR_XT_TMPDIR_00000:userlist.badDISCARDFILE SYS_SQLLDR_XT_TMPDIR_00000:userlist.disLOGFILE user.log_xtREADSIZE 1048576FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY LDRTRIMMISSING FIELD VALUES ARE NULLREJECT ROWS WITH ALL NULL FIELDS(ID CHAR(30)TERMINATED BY , OPTIONALLY ENCLOSED BY ,USERNAME CHAR(30)TERMINATED BY , OPTIONALLY ENCLOSED BY ))location(userlist.txt))REJECT LIMIT UNLIMITED...瞧原来我们更本不用担心怎么写create external table的语句呢。sqlldr就可以帮我们生成了您是不是已经学会了
