oracel external table-CSDN博客

本文介绍Oracle外表的创建及使用过程，包括创建目录、授予权限、创建外表并进行数据查询，同时解析了外表数据结构及CREATE TABLE...ORGANIZATION EXTERNAL语法。

2008-08-19 作者:bitsCN整理来源:中国网管联盟【转自www.bitsCN.com】

外表(external table)就像普通的表对像一样，可以select等，只是它是只读的，数据库中只保存了表结构的描述，表数据却没有存放在数据库内，而是存放在了文件系统上。当用户想偶尔使用数据库外的结构化数据时，用起外表来就非常方便，甚至比sqlldr都要方便的多。在这篇文章里，我们为大家演示了

　　三步就掌握oracle外表过程。通过这次学习，也许大家就会发展原来学习oracle也是好容易哦。

　　第一步：创建目录并授权

　　目录是数据文件的存放目标，数据文件通常要求是文本文件。这个过程在9i以前是需要配置utl_file_dir参数的。复制内容到剪贴板

　　代码:　

　　sys@TEST>!ls /home/oracle/temp

　　user.ctl userlist.txt user.log

　　rudolf@TEST >

　　sys@TEST >conn system/alibaba

　　Connected.

　　sys@TEST >

　　sys@TEST >CREATE DIRECTORY TEMP AS '/home/oracle/temp/';

　　Directory created.

　　sys@TEST >grant read,write on directory TEMP to rudolf;

　　Grant succeeded.

　　第二步：创建外表与测试复制内容到剪贴板

　　代码:　　

　　rudolf@TEST>CREATE TABLE "USERLIST"

　　2 (

　　3 ID NUMBER,

　　4 USERNAME VARCHAR2(30),

　　5 EMAIL VARCHAR2(128)

　　6 )

　　7 ORGANIZATION external

　　8 (

　　9 TYPE oracle_loader

　　10 DEFAULT DIRECTORY TEMP 11 ACCESS PARAMETERS

　　12 (

　　13 RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII

　　14 BADFILE 'TEMP':'userlist.bad'

　　15 DISCARDFILE 'TEMP':'userlist.dis'

　　16 LOGFILE 'TEMP':'user.log'

　　17 READSIZE 1048576

　　18 FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM

　　19 MISSING FIELD VALUES ARE NULL

　　20 REJECT ROWS WITH ALL NULL FIELDS

　　21 (

　　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 location

　　31 (

　　32 'userlist.txt'

　　33 )

　　34 )REJECT LIMIT UNLIMITED

　　rudolf@TEST >/

　　Table created.

　　rudolf@TEST >l

　　1 select id,username from userlist where rownum < 10

　　rudolf@TEST >/

　　ID USERNAME

　　---------- ------------------------------

　　1 RudolfLu

　　3 tomgu

　　6 coug

　　7 chao_ping

　　8 parrotao

　　9 **

　　10 FilsDeDragon

　　11 Dragon

　　9 rows selected.

　　瞧，成功了。外表就这么简单。可是只有二步啊，第三步在哪里呢?你也许会问。还有啊，userlist.txt要固定的格式吗?create table...的语法这样的狂复杂，每一项都是什么含义呢?

　　这就是第三步要教给大家的东西了。第三步：理解外表数据结构与create table ... organization external语法

　　大家都用过sqlldr吧?外表的数据文件的结构呢就同sqlldr能读的数据文件结构一样了。那么语法呢?嘿嘿，别急，让我们先来做个sqlldr的练习吧：

　　[oracle@rac1 temp]$ head -10 userlist.txt

　　1,"RudolfLu"

　　3, "tomgu"

　　6, "coug"

　　7, "chao_ping"

　　8, "parrotao"

　　9, "**"

　　10, "FilsDeDragon"

　　11, "Dragon"

　　15, "Xavier"

　　[oracle@rac1 temp]$ cat user.ctl

　　LOAD

　　INFILE '/home/oracle/temp/userlist.txt'

　　badfile '/home/oracle/temp/userlist.bad'

　　discardfile '/home/oracle/temp/userlist.dis'

　　APPEND

　　INTO TABLE userlist

　　fields terminated by ',' optionally enclosed by ' "'

　　trailing nullcols

　　( id char(30),

　　username char(30)

　　)

　　rudolf@TEST >create table userlist

　　2 (id number,

　　3 username varchar2(30)

　　4 );

　　Table created.

　　rudolf@TEST >!

　　[oracle@rac1 temp]$ sqlldr rudolf/nix@test2.world control=./user.ctl external_table=GENERATE_ONLY

　　注意，我们加了一个external_table的参数。它的作用是告诉sqlldr不用真实load数据，而是生成包含external table 创建脚本的log文件。

　　[oracle@rac1 temp]$ ls

　　user.ctl userlist.txt user.log

　　[oracle@rac1 temp]$ cat user.log

　　SQL*Loader: Release 9.2.0.4.0 - Production on Wed Dec 10 20:50:19 2003

　　Control File: ./user.ctl

　　Data File: /home/oracle/temp/userlist.txt

　　Bad File: /home/oracle/temp/userlist.bad

　　Discard 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_loader

　　DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000

　　ACCESS PARAMETERS

　　(

　　RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII

　　BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'userlist.bad'

　　DISCARDFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'userlist.dis'

　　LOGFILE 'user.log_xt'

　　READSIZE 1048576

　　FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LDRTRIM

　　MISSING FIELD VALUES ARE NULL

　　REJECT 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就可以帮我们生成了!

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/14855094/viewspace-446913/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/14855094/viewspace-446913/