教会你掌握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.　　
　　
　　第三步：理解外表数据结构与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　　...