教會你掌握oracle外表
2024-08-29 13:40:54
供稿：網友
外表(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 ...
