三步教你掌握Oracle外部表

教你掌握Oracle外部表"/>

三步教你掌握Oracle外部表

外表(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就可以帮我们生成了!