外表（external table）就像普通的表对像一样可以select等只是它是只读的数据库中只保存了表结构的描述表数据却没有存放在数据库内而是存放在了文件系统上当用户想偶尔使用数据库外的结构化数据时用起外表来就非常方便甚至比sqlldr都要方便的多在这篇文章里我们为大家演示了
三步就掌握Oracle外表过程通过这次学习也许大家就会发展原来学习oracle也是好容易哦
第一步创建目录并授权
目录是数据文件的存放目标数据文件通常要求是文本文件这个过程在i以前是需要配置utl_file_dir参数的 复制内容到剪贴板
代码
!ls>sys@TEST>!ls /home/oracle/temp
userctl userlisttxt userlog
rudolf@TEST>
conn>sys@TEST>conn system/alibaba
Connected
sys@TEST>
CREATE>sys@TEST>CREATE DIRECTORY TEMP AS /home/oracle/temp/;
Directory created
grant>sys@TEST>grant readwrite on directory TEMP to rudolf;
Grant succeeded
第二步创建外表与测试 复制内容到剪贴板
代码
cellPadding= width=% bgColor=#ffffff border=>CREATE>rudolf@TEST>CREATE TABLE USERLIST
(
ID NUMBER
USERNAME VARCHAR()
EMAIL VARCHAR()
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY TEMP
Access PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET USASCII
BADFILE TEMP:userlistbad
DISCARDFILE TEMP:userlistdis
LOGFILE TEMP:userlog
READSIZE
FIELDS TERMINATED BY OPTIONALLY ENCLOSED BY LDRTRIM
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
(
ID CHAR()
TERMINATED BY OPTIONALLY ENCLOSED BY
cellPadding= width=% bgColor=#ffffff border=> USERNAME CHAR()
TERMINATED BY OPTIONALLY ENCLOSED BY
EMAIL CHAR()
TERMINATED BY OPTIONALLY ENCLOSED BY
)
)
location
(
userlisttxt
)
)REJECT LIMIT UNLIMITED
/>rudolf@TEST>/
Table created
l>rudolf@TEST>l
select idusername from userlist where rownum <
*
/>rudolf@TEST>/
ID USERNAME
RudolfLu
tomgu
coug
chao_ping
parrotao
cnoug
FilsDeDragon
Dragon
rows selected
瞧成功了外表就这么简单可是只有二步啊第三步在哪里呢？你也许会问还有啊userlisttxt要固定的格式吗？create table……的语法这样的狂复杂每一项都是什么含义呢？
这就是第三步要教给大家的东西了
第三步理解外表数据结构与create table …… organization external语法
大家都用过sqlldr吧？外表的数据文件的结构呢就同sqlldr能读的数据文件结构一样了那么语法呢？嘿嘿别急让我们先来做个sqlldr的练习吧
cellPadding= width=% bgColor=#ffffff border=>[oracle@rac temp]$ head userlisttxt
RudolfLu
tomgu
coug
chao_ping
parrotao
cnoug
FilsDeDragon
Dragon
Xavier
[oracle@rac temp]$ cat userctl
LOAD
INFILE /home/oracle/temp/userlisttxt
badfile /home/oracle/temp/userlistbad
discardfile /home/oracle/temp/userlistdis
cellPadding= width=% bgColor=#ffffff border=>APPEND
INTO TABLE userlist
fields terminated by optionally enclosed by
trailing nullcols
( id char()
username char()
)
create>rudolf@TEST>create table userlist
(id number
username varchar()
);
Table created
rudolf@TEST>!
[oracle@rac temp]$ sqlldr rudolf/nix@testworld control=/userctl external_table=GENERATE_ONLY
注意
我们加了一个external_table的参数
它的作用是告诉sqlldr不用真实load数据
而是生成包含external table 创建脚本的log文件
cellPadding= width=% bgColor=#ffffff border=>[oracle@rac temp]$ ls
userctl userlisttxt userlog
[oracle@rac temp]$ cat userlog
SQL*Loader: Release Production on Wed Dec ::
Copyright (c) Oracle Corporation All rights reserved
Control File: /userctl
Data File: /home/oracle/temp/userlisttxt
Bad File: /home/oracle/temp/userlistbad
Discard File: /home/oracle/temp/userlistdis
CREATE DIRECTORY statements needed for files
CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_ AS /home/oracle/temp/
CREATE TABLE statement for external table:
CREATE TABLE SYS_SQLLDR_X_EXT_USERLIST
(
ID NUMBER
USERNAME VARCHAR()
)
ORGANIZATION external
(
TYPE oracle_loader cellPadding= width=% bgColor=#ffffff border=>DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET USASCII
BADFILE SYS_SQLLDR_XT_TMPDIR_:userlistbad
DISCARDFILE SYS_SQLLDR_XT_TMPDIR_:userlistdis
LOGFILE userlog_xt
READSIZE
FIELDS TERMINATED BY OPTIONALLY ENCLOSED BY LDRTRIM
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
(
ID CHAR()
TERMINATED BY OPTIONALLY ENCLOSED BY
USERNAME CHAR()
TERMINATED BY OPTIONALLY ENCLOSED BY )
)
location
(
userlisttxt
)
)REJECT LIMIT UNLIMITED
瞧原来我们更本不用担心怎么写create external table的语句呢sqlldr就可以帮我们生成了！
您是不是已经学会了？
