教會你掌握oracle外表

　　外表(external table)就像普通的表對像一樣可以select等只是它是只讀的數據庫中只保存了表結構的描述表數據卻沒有存放在數據庫內而是存放在了文件系統上當用戶想偶爾使用數據庫外的結構化數據時用起外表來就非常方便甚至比sqlldr都要方便的多在這篇文章裡我們為大家演示了三步就掌握oracle外表過程通過這次學習也許大家就會發展原來學習oracle也是好容易哦
　　
　　第一步創建目錄並授權
　　目錄是數據文件的存放目標數據文件通常要求是文本文件這個過程在i以前是需要配置utl_file_dir參數的
　　
　　　　sys@TEST>!ls /home/oracle/temp
　　　userctl　userlisttxt　userlog
　　　　rudolf@TEST>　sys@TEST>conn system/alibaba
　　　Connected　sys@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
　　　 LOAD　INFILE /home/oracle/temp/userlisttxt
　　　badfile /home/oracle/temp/userlistbad
　　　discardfile /home/oracle/temp/userlistdis
　　　APPEND　INTO TABLE userlist　fields terminated by
　　 optionally enclosed by
　　　trailing nullcols
　　　( id　char()
　　　　username char()　)
　　　　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文件
　　
　　　[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
　　　　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　　
From:http://tw.wingwit.com/Article/program/Oracle/201311/18672.html