ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 三步教会你掌握oracle外表(external table)

三步教会你掌握oracle外表(external table)

原创 Linux操作系统 作者:west_wolf6 时间:2009-05-19 16:19:15 0 删除 编辑
外表(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 **
      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,"**"
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就可以帮我们生成了! 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16942186/viewspace-600468/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
下一篇: newhappy2008
请登录后发表评论 登录
全部评论

注册时间:2009-05-19

  • 博文量
    5
  • 访问量
    4563