教会你掌握oracle外表

王朝oracle·作者佚名  2008-05-31
窄屏简体版  字體: |||超大  

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

在这篇文章里,我们为大家演示了三步就把握Oracle外表过程。通过这次学习,也许大家就会发展原来学习oracle也是好轻易哦。

第一步:创建目录并授权

目录是数据文件的存放目标,数据文件通常要求是文本文件。这个过程在9i以前是需要配置utl_file_dir参数的。

sys@TEST!ls /home/oracle/temp

user.ctluserlist.txtuser.log

rudolf@TESTsys@TESTconn system/alibaba

Connected.sys@TEST

sys@TESTCREATE DirectorY

TEMP AS '/home/oracle/temp/';

Directory created.

sys@TESTgrant read,write on directory TEMP to rudolf;

Grant sUCceeded.

第二步:创建外表与测试

rudolf@TESTCREATE TABLE "USERLIST"

2(

3ID NUMBER,

4USERNAME VARCHAR2(30),

5EMAIL VARCHAR2(128)

6)

7ORGANIZATION external

8(

9TYPE oracle_loader

10DEFAULT DIRECTORY TEMP

11Access PARAMETERS

12(

13RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII

14BADFILE 'TEMP':'userlist.bad'

15DISCARDFILE 'TEMP':'userlist.dis'

16LOGFILE 'TEMP':'user.log'

17READSIZE 1048576

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

19MISSING FIELD VALUES ARE NULL

20REJECT ROWS WITH ALL NULL FIELDS

21(

22ID CHAR(30)

23TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',

24USERNAME CHAR(30)

25TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',

26EMAIL CHAR(128)

27TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'

28)

29)

30location

31(

32'userlist.txt'

33)

34)REJECT LIMIT UNLIMITED

35rudolf@TEST/

Table created.

rudolf@TESTl

1select id,username from userlist where rownum < 10

2*rudolf@TEST/

ID USERNAME

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

1 RudolfLu

3 tomgu

6 coug

7 chao_ping

8 parrotao

9 cnoug

10 FilsDeDragon

11 Dragon

9 rows selected.

第三步:理解外表数据结构与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,"cnoug"

10,"FilsDeDragon"

11,"Dragon"

15,"Xavier"

[oracle@rac1 temp]$ cat user.ctl

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

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

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

APPENDINTO TABLE userlistfields terminated by ','

optionally enclosed by '"'

trailing nullcols

( idchar(30),

username char(30))

rudolf@TESTcreate table userlist

2(idnumber,

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

Copyright (c) 1982, 2002, Oracle Corporation.

All rights reserved.

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...

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
© 2005- 王朝網路 版權所有  導航