外表(external table)就像普通的表对像一样可以select等只是它是只读的数据库中只保存了表结构的描述表数据却没有存放在数据库内而是存放在了文件系统上当用户想偶尔使用数据库外的结构化数据时用起外表来就非常方便甚至比sqlldr都要方便的多在这篇文章里我们为大家演示了三步就掌握oracle外表过程通过这次学习也许大家就会发展原来学习oracle也是好容易哦
第一步创建目录并授权
目录是数据文件的存放目标数据文件通常要求是文本文件这个过程在i以前是需要配置utl_file_dir参数的
sys@TEST>!ls /home/oracle/temp
userctluserlisttxtuserlog
rudolf@TEST>sys@TEST>conn system/alibaba
Connectedsys@TEST>
sys@TEST>CREATE DIRECTORY
TEMP AS /home/oracle/temp/;
Directory created
sys@TEST>grant readwrite on directory TEMP to rudolf;
Grant succeeded
第二步创建外表与测试
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
USERNAME CHAR()
TERMINATED BY OPTIONALLY ENCLOSED BY
EMAIL CHAR()
TERMINATED BY OPTIONALLY ENCLOSED BY
)
)
location
(
userlisttxt
)
)REJECT LIMIT UNLIMITED
rudolf@TEST>/
Table created
rudolf@TEST>l
select idusername from userlist where rownum <
*rudolf@TEST>/
ID USERNAME
RudolfLu
tomgu
coug
chao_ping
parrotao
cnoug
FilsDeDragon
Dragon
rows selected
第三步理解外表数据结构与create table organization external语法
大家都用过sqlldr吧？外表的数据文件的结构呢就同sqlldr能读的数据文件结构一样了那么语法呢？嘿嘿别急让我们先来做个sqlldr的练习吧
[oracle@rac temp]$ head userlisttxt
RudolfLu
tomgu
coug
chao_ping
parrotao
cnoug
FilsDeDragon
Dragon
Xavier
[oracle@rac temp]$ cat userctl
LOADINFILE /home/oracle/temp/userlisttxt
badfile /home/oracle/temp/userlistbad
discardfile /home/oracle/temp/userlistdis
APPENDINTO TABLE userlistfields terminated by
optionally enclosed by
trailing nullcols
( idchar()
username char())
rudolf@TEST>create table userlist
(idnumber
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文件
[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 NUMBERUSERNAME VARCHAR())
ORGANIZATION external
(
TYPE oracle_loader
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
