外部表只能在Oracle 9i之后來(lái)使用。簡(jiǎn)單地說(shuō)，外部表，是指不存在于數據庫中的表。通過(guò)向Oracle提供描述外部表的元數據，我們
可以把一個(gè)操作系統文件當成一個(gè)只讀的數據庫表，就像這些數據存儲在一個(gè)普通數據庫表中一樣來(lái)進(jìn)行訪(fǎng)問(wèn)。外部表是對數據庫表的延伸。
一、外部表的特性
位于文件系統之中，按一定格式分割，如文本文件或者其他類(lèi)型的表可以作為外部表。
對外部表的訪(fǎng)問(wèn)可以通過(guò)SQL語(yǔ)句來(lái)完成，而不需要先將外部表中的數據裝載進(jìn)數據庫中。
外部數據表都是只讀的，因此在外部表不能夠執行DML操作，也不能創(chuàng )建索引。
ANALYZE語(yǔ)句不支持采集外部表的統計數據，應該使用DMBS_STATS包來(lái)采集外部表的統計數據。
二、創(chuàng )建外部表的注意事項
1.需要先建立目錄對象
2.對于操作系統文件的要求
文件要有固定的格式、不能有標題列、訪(fǎng)問(wèn)時(shí)會(huì )自動(dòng)創(chuàng )建一個(gè)日志文件
3.在建立臨時(shí)表時(shí)的相關(guān)限制
對表中字段的名稱(chēng)存在特殊字符的情況下，必須使用英文狀態(tài)的下的雙引號將該表列名稱(chēng)連接起來(lái)。如采用”SalseID#”。
對于列名字中特殊符號未采用雙引號括起來(lái)時(shí)，會(huì )導致無(wú)法正常查詢(xún)數據。
建議不用使用特殊的列標題字符
在創(chuàng )建外部表的時(shí)候，并沒(méi)有在數據庫中創(chuàng )建表，也不會(huì )為外部表分配任何的存儲空間。
創(chuàng )建外部表只是在數據字典中創(chuàng )建了外部表的元數據，以便對應訪(fǎng)問(wèn)外部表中的數據，而不在數據庫中存儲外部表的數據。
簡(jiǎn)單地說(shuō)，數據庫存儲的只是與外部文件的一種對應關(guān)系，如字段與字段的對應關(guān)系。而沒(méi)有存儲實(shí)際的數據。
由于存儲實(shí)際數據，故無(wú)法為外部表創(chuàng )建索引，同時(shí)在數據使用DML時(shí)也不支持對外部表的插入、更新、刪除等操作。
4.刪除外部表或者目錄對象
一般情況下，先刪除外部表，然后再刪除目錄對象，如果目錄對象中有多個(gè)表，應刪除所有表之后再刪除目錄對象。
如果在未刪除外部表的情況下，強制刪除了目錄，在查詢(xún)到被刪除的外部表時(shí)，將收到"對象不存在"的錯誤信息。
查詢(xún)dba_external_locations來(lái)獲得當前所有的目錄對象以及相關(guān)的外部表，同時(shí)會(huì )給出這些外部表所對應的操作系統文件的名字。
5.對于操作系統平臺的限制
不同的操作系統對于外部表有不同的解釋和顯示方式
如在Linux操作系統中創(chuàng )建的文件是分號分隔且每行一條記錄，但該文件在Windows操作系統上打開(kāi)則并非如此。
建議避免不同操作系統以及不同字符集所帶來(lái)的影響
三、創(chuàng )建外部表
使用CREATE TABLE語(yǔ)句的ORGANIZATION EXTENERAL子句來(lái)創(chuàng )建外部表。外部表不分配任何盤(pán)區，因為僅僅是在數據字典中創(chuàng )建元數據。
1.外部表的創(chuàng )建語(yǔ)法
createtabletable_name
(col1 datatype1,col2 datatype2,col3 datatype3)
organization exteneral
(.....)
2.由查詢(xún)結果集，使用Oracle_datapump來(lái)填充數據來(lái)生成外部表
a.創(chuàng )建系統目錄以及Oracle數據目錄名來(lái)建立對應關(guān)系，同時(shí)授予權限
[oracle@oradb ~]$ mkdir-p/home/oracle/external_tb/data
sys@ORCL>createorreplacedirectory dat_diras'/home/oracle/external_tb/data/';
sys@ORCL>grantread,writeondirectory dat_dirtoscott;
b.創(chuàng )建外部表
scott@ORCL>createtableex_tb1 --創(chuàng )建外部表
2 (ename,job,sal,dname) --表列描述，注意未指定數據類(lèi)型
3 organizationexternal
4 (
5 typeoracle_datapump --使用datapump將查詢(xún)結果填充到外部表,注,此處由select生成，故不支持oracle_loader
6 defaultdirectory dat_dir --指定外部表的存放目錄
7 location('tb1.exp','tb2.exp') --產(chǎn)生外部表的內容將填充到這些文件中
8 )
9 parallel --按并行方式來(lái)填充
10 as
11 selectename,job,sal,dname -填充使用的原始數據
12 fromempjoindept
13 onemp.deptno=dept.deptno;
c.--驗證外部表
scott@ORCL>select*fromex_tb1;
ENAME JOB SAL DNAME
---------- --------- ---------- --------------
SMITH CLERK 800 RESEARCH
ALLEN SALESMAN 1600 SALES
WARD SALESMAN 1250 SALES
JONES MANAGER 2975 RESEARCH
..........
對于使用上述方式創(chuàng )建的外部表可以將其復制到其他路徑作為外部表的原始數據來(lái)生成新的外部表，用于轉移數據。
3.使用SQLLDR提供外部表的定義并創(chuàng )建外部表
關(guān)于SQL*Loader的使用請參照：SQL*Loader使用方法
我們使用SQL*Loader和下面的這個(gè)控制文件來(lái)生成外部表的定義
[oracle@oradb ~]$ cat demo1.ctl
LOADDATA
INFILE*
INTOTABLEDEPT_NEW
FIELDS TERMINATEDBY','
(DEPTNO,DNAME,LOC)
BEGINDATA
10,Sales,Virginia
20,Accounting,Virginia
30,Consulting,Virginia
40,Finance,Virginia
[oracle@oradb ~]$ sqlldr scott/tiger control=demo1.ctl external_table=generate_only
EXTERNAL_TABLE參數有以下三個(gè)值：
NOT_USED：默認值。
EXECUTE：這個(gè)值說(shuō)明SQLLDR不會(huì )生成并執行一個(gè)SQLINSERT語(yǔ)句；而是會(huì )創(chuàng )建一個(gè)外部表，且使用一個(gè)批量SQL語(yǔ)句來(lái)加載。
GENERATE_ONLY：使SQLLDR并不具體加載任何數據，而只是會(huì )生成所執行的SQL DDL和DML語(yǔ)句，并放到它創(chuàng )建的日志文件中。
注：DIRECT=TRUE覆蓋EXTENAL_TABLE=GENERATE_ONLY。如果指定了DIRECT=TRUE，則會(huì )加載數據，而不會(huì )生成外部表。
[oracle@oradb ~]$ cat demo1.log --查看sqlldr產(chǎn)生的日志文件
TableDEPT_NEW,loadedfromevery logical record.
Insertoptionineffectforthistable:INSERT
ColumnName Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
DEPTNO FIRST * , CHARACTER
DNAME NEXT * , CHARACTER
LOC NEXT * , CHARACTER
CREATEDIRECTORY statements neededforfiles --創(chuàng )建一個(gè)目錄
------------------------------------------------------------------------
CREATEDIRECTORY SYS_SQLLDR_XT_TMPDIR_00000AS'/home/oracle/'
CREATETABLEstatementforexternaltable: --生成創(chuàng )建外部表的命令
------------------------------------------------------------------------
CREATETABLE"SYS_SQLLDR_X_EXT_DEPT_NEW"
(
"DEPTNO" NUMBER(2),
"DNAME" VARCHAR2(20),
"LOC" VARCHAR2(20)
)
ORGANIZATIONexternal --該子句表明是一個(gè)外部表heap對應普通表，index對應iot，external對應外部表
(
TYPEoracle_loader --說(shuō)明外部文件訪(fǎng)問(wèn)方式：oracle_loader或oracle_datapump(9i不支持)
DEFAULTDIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 --指定外部文件的缺省目錄
ACCESS PARAMETERS --這個(gè)訪(fǎng)問(wèn)參數有些類(lèi)似于sqlldr中控制文件中的描述信息
( --系統根據這些描述信息來(lái)生成外部表的格式
RECORDS DELIMITEDBYNEWLINE CHARACTERSET US7ASCII --記錄默認以換行符結束
BADFILE'SYS_SQLLDR_XT_TMPDIR_00000':'demo1.bad' --存放處理失敗的記錄文件描述
LOGFILE'demo1.log_xt' --日志文件
READSIZE 1048576--Oracle讀取輸入數據文件所用的默認緩沖區,此處為MB,如專(zhuān)用模式則從PGA分配,如共享模式則從SGA分配
SKIP 6 --跳過(guò)的記錄數，因為我們使用了控制文件，所以前面的控制信息需要跳過(guò)
FIELDS TERMINATEDBY"," LDRTRIM --描述字段的終止符
REJECT ROWSWITHALLNULLFIELDS --所有為空值的行被跳過(guò)并且記錄到bad file.
( --下面是描述外部文件各個(gè)列的定義
"DEPTNO"CHAR(255)
TERMINATEDBY",",
"DNAME"CHAR(255)
TERMINATEDBY",",
"LOC"CHAR(255)
TERMINATEDBY","
)
)
location
(
'demo1.ctl' --描述外部文件的文件名
)
)REJECT LIMIT UNLIMITED --描述允許的錯誤數，此處為無(wú)限制
INSERTstatements usedtoloadinternal tables: --用于將數據填充到表，使用append方式
------------------------------------------------------------------------
INSERT/*+ append */INTODEPT_NEW
(
DEPTNO,
DNAME,
LOC
)
SELECT
"DEPTNO",
"DNAME",
"LOC"
FROM"SYS_SQLLDR_X_EXT_DEPT"
statementstocleanup objects createdbyprevious statements: --用于刪除目錄和外部表的定義信息
------------------------------------------------------------------------
DROPTABLE"SYS_SQLLDR_X_EXT_DEPT_NEW"
DROPDIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
-----------------------------------------------------------------------------------------------------------------------
sys@ORCL>grantcreateanydirectorytoscott;
sys@ORCL>grantdropanydirectorytoscott;
scott@ORCL>createtabledept_new
2 (deptno number,dname varchar2(20),loc varchar2(25));
scott@ORCL>select*fromdept_new;
norows selected
[oracle@oradb ~]$ sqlldr scott/tiger control=demo1.ctl external_table=execute
scott@ORCL>select*fromdept_new;
DEPTNO DNAME LOC
---------- -------------------- -------------------------
10 Sales Virginia
20 Accounting Virginia
30 Consulting Virginia
40 Finance Virginia
4.使用平面文件定義并生成外部表
a.平面文件數據
1.dat：
7369,SMITH,CLERK,7902,17-DEC-80,100,0,20
7499,ALLEN,SALESMAN,7698,20-FEB-81,250,0,30
7521,WARD,SALESMAN,7698,22-FEB-81,450,0,30
7566,JONES,MANAGER,7839,02-APR-81,1150,0,20
2.dat：
7654,MARTIN,SALESMAN,7698,28-SEP-81,1250,0,30
7698,BLAKE,MANAGER,7839,01-MAY-81,1550,0,30
7934,MILLER,CLERK,7782,23-JAN-82,3500,0,10
b.繼續使用前面創(chuàng )建的目錄/home/oracle/external_tb/data來(lái)存放數據文件：
sys@ORCL>select*fromdba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
--------------- --------------- ---------------------------------------------
SYS DATA_PUMP_DIR /u01/oracle/10g/rdbms/log/
SYS DAT_DIR /home/oracle/external_tb/data/
scott@ORCL>ho ls/home/oracle/external_tb/data/
1.dat 2.dat tb1.exp tb2.exp
c.創(chuàng )建外部表
scott@ORCL>get/u01/bk/scripts/tb.emp_new
1 createtableemp_new
2 (
3 emp_id number(4),
4 ename varchar2(15),
5 job varchar2(12),
6 mgr_id number(4),
7 hiredate date,
8 salary number(8),
9 comm number(8),
10 dept_id number(2)
11 )
12 organizationexternal
13 (
14 typeoracle_loader
15 defaultdirectory dat_dir
16 access parameters
17 (
18 records delimitedbynewline
19 fields terminatedby','
20 )
21 location
22 ('1.dat','2.dat')
23*);
scott@ORCL>start/u01/bk/scripts/tb.emp_new
d.驗證外部表
scott@ORCL>select*fromemp_new;
EMP_ID ENAME JOB MGR_ID HIREDATE SALARY COMM DEPT_ID
---------- --------------- ------------ ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 100 0 20
7499 ALLEN SALESMAN 7698 20-FEB-81 250 0 30
............................
scott@ORCL>deletefromemp_newwhereename='SMITH'; --外部表不能執行DML
deletefromemp_newwhereename='SMITH'
*
ERROR at line 1:
ORA-30657:operationnotsupportedonexternalorganizedtable
scott@ORCL>insertintoemp_new(emp_id,ename)select8888,'Robinson'fromdual;
insertintoemp_new(emp_id,ename)select8888,'Robinson'fromdual
*
ERROR at line 1:
ORA-30657:operationnotsupportedonexternalorganizedtable
e.獲得外部表的有關(guān)信息：
scott@ORCL>col access_parameters format a35
scott@ORCL>selectowner,table_name,type_name,default_directory_name,access_parameters
2 fromdba_external_tables;
OWNER TABLE_NAME TYPE_NAME DEFAULT_DIRECTO ACCESS_PARAMETERS
---------- --------------- ------------------------------ --------------- -----------------------------------
SCOTT EX_TB1 ORACLE_DATAPUMP DAT_DIR
SCOTT EMP_NEW ORACLE_LOADER DAT_DIR records delimitedbynewline
fields terminatedby','
SCOTT EMP_PUMP ORACLE_DATAPUMP DAT_DIR records delimitedbynewline
fields terminatedby','
f.獲得平面文件的位置，使用如下的查詢(xún)：
scott@ORCL>select*fromdba_external_locationsorderbytable_name;
OWNER TABLE_NAME LOCATION DIR DIRECTORY_NAME
---------- --------------- --------------- --- ------------------------------
SCOTT EMP_NEW 1.dat SYS DAT_DIR
SCOTT EMP_NEW 2.dat SYS DAT_DIR
SCOTT EMP_PUMP 1.dat SYS DAT_DIR
SCOTT EMP_PUMP 2.dat SYS DAT_DIR
SCOTT EX_TB1 tb2.exp SYS DAT_DIR
SCOTT EX_TB1 tb1.exp SYS DAT_DIR
5.外部表定義的進(jìn)一步分析
CREATETABLEexternal_table
(
COL01 VARCHAR2(100),
COL02 NUMBER,
......
)
ORGANIZATIONEXTERNAL
(
TYPEORACLE_LOADER
DEFAULTDIRECTORY "XXX"
ACCESS PARAMETERS
(
RECORDS DELIMITEDBY0X'0A'
SKIP 1
BADFILE'bad.txt'
FIELDS TERMINATEDBY','
OPTIONALLY ENCLOSEDBY'"'
LRTRIM MISSING FIELDVALUESARENULL
REJECT ROWSWITHALLNULLFIELDS
)
LOCATION
("CJ_DIR":'data.txt')
)REJECT LIMIT UNLIMITED;
外部表定義的幾個(gè)重點(diǎn)
a.ORGANIZATIONEXTERNAL關(guān)鍵字，必須要有。以表明定義的表為外部表。
b.重要參數外部表的類(lèi)型
ORACLE_LOADER：定義外部表的缺省方式，只能只讀方式實(shí)現文本數據的裝載。
ORACLE_DATAPUMP：支持對數據的裝載與卸載，數據文件必須為二進(jìn)制dump文件?？梢詮耐獠勘硖崛祿b載到內部表，也
可以從內部表卸載數據作為二進(jìn)制文件填充到外部表。
c.DEFAULTDIRECTORY：缺省的目錄指明了外部文件所在的路徑
d.LOCATION：定義了外部表的位置
f.ACCESS PARAMETERS：描述如何對外部表進(jìn)行訪(fǎng)問(wèn)
RECORDS關(guān)鍵字后定義如何識別數據行
DELIMITEDBY'XXX'——換行符，常用newline定義換行，并指明字符集。對于特殊的字符則需要單獨定義，
如特殊符號，可以使用OX'十六位值'，例如tab(/t)的十六位是9，則DELIMITEDBY0X'09'；
cr(/r)的十六位是d，那么就是DELIMITEDBY0X'0D'。
SKIP X ——跳過(guò)X行數據，有些文件中第一行是列名，需要跳過(guò)第一行，則使用SKIP 1。
FIELDS關(guān)鍵字后定義如何識別字段，常用的如下：
FIELDS：TERMINATEDBY'x'——字段分割符。
ENCLOSEDBY'x'——字段引用符，包含在此符號內的數據都當成一個(gè)字段。
例如一行數據格式如："abc","a""b,""c,"。使用參數TERMINATEDBY','ENCLOSEDBY'"'后，系統會(huì )讀到兩個(gè)字段，
第一個(gè)字段的值是abc，第二個(gè)字段值是a"b,"c,。
LRTRIM ——刪除首尾空白字符。
MISSING FIELDVALUESARENULL——某些字段空缺值都設為NULL。
對于字段長(cháng)度和分割符不確定且準備用作外部表文件，可以使用UltraEdit、Editplus等來(lái)進(jìn)行分析測試，如果文件較
大，則需要考慮將文件分割成小文件并從中提取數據進(jìn)行測試。
外部表對錯誤的處理
REJECT LIMIT UNLIMITED
在創(chuàng )建外部表時(shí)最后加入LIMIT子句，表示可以允許錯誤的發(fā)生個(gè)數。默認值為零。設定為UNLIMITED則錯誤不受限制
BADFILE和NOBADFILE子句
用于指定將捕獲到的轉換錯誤存放到哪個(gè)文件。如果指定了NOBADFILE則表示忽略轉換期間的錯誤
如果未指定該參數，則系統自動(dòng)在源目錄下生成與外部表同名的.BAD文件
BADFILE記錄本次操作的結果，下次將會(huì )被覆蓋
LOGFILE和NOLOGFILE子句
同樣在accessparameters中加入LOGFILE'LOG_FILE.log'子句，則所有Oracle的錯誤信息放入'LOG_FILE.log'中
而NOLOGFILE子句則表示不記錄錯誤信息到log中，如忽略該子句，系統自動(dòng)在源目錄下生成與外部表同名的.LOG文件
注意以下幾個(gè)常見(jiàn)的問(wèn)題
1.外部表經(jīng)常遇到BUFFER不足的情況，因此盡可能的增大READSIZE
2.換行符不對產(chǎn)生的問(wèn)題。在不同的操作系統中換行符的表示方法不一樣，碰到錯誤日志提示如是換行符問(wèn)題，可以使用
UltraEdit打開(kāi)，直接看十六進(jìn)制
3.特定行報錯時(shí)，查看帶有"BAD"的日志文件,其中保存了出錯的數據,用記事本打開(kāi)看看那里出錯,是否存在于外部表定義相沖突
聯(lián)系客服
