三步教你掌握Oracle外表(external table)_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛_联动北方技术论坛  
网站首页 | 关于我们 | 服务中心 | 经验交流 | 公司荣誉 | 成功案例 | 合作伙伴 | 联系我们 |
联动北方-国内领先的云技术服务提供商
»  游客             当前位置:  论坛首页 »  自由讨论区 »  MySQL, Oracle及数据库讨论区 »
总帖数
1
每页帖数
101/1页1
返回列表
0
发起投票  发起投票 发新帖子
查看: 2595 | 回复: 0   主题: 三步教你掌握Oracle外表(external table)        下一篇 
cc
注册用户
等级:中校
经验:1900
发帖:195
精华:0
注册:2011-7-25
状态:离线
发送短消息息给cc 加好友    发送短消息息给cc 发消息
发表于: IP:您无权察看 2015-3-30 16:20:47 | [全部帖] [楼主帖] 楼主

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

第一步:创建目录并授权

目录是数据文件的存放目标,数据文件通常要求是文本文件。这个过程在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.


成功了。

第三步:理解外表数据结构与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就可以帮我们生成了!

--转自 北京联动北方科技有限公司




赞(0)    操作        顶端 
总帖数
1
每页帖数
101/1页1
返回列表
发新帖子
请输入验证码: 点击刷新验证码
您需要登录后才可以回帖 登录 | 注册
技术讨论