|
【赛迪网－it技术报道】外表(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
35
rudolf@test>/
table created.
rudolf@test>l
1 select 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.
瞧，成功了。外表就这么简单。可是只有二步啊，第三步在哪里呢？你也许会问。还有啊，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,"cnoug"
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
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
...
瞧，原来我们更本不用担心怎么写create external table的语句呢。sqlldr就可以帮我们生成了！
您是不是已经学会了？ |
