oracle外部表詳解以及使用

小小黃-812發表於2013-12-20
Oracle

外部表詳解：
外部表概述

外部表只能在Oracle 9i之後來使用。簡單地說，外部表，是指不存在於資料庫中的表。通過向Oracle提供描述外部表的後設資料，我們可以把一個作業系統檔案當成一個只讀的資料庫表，就像這些資料儲存在一個普通資料庫表中一樣來進行訪問。外部表是對資料庫表的延伸。
外部表的特性
位於檔案系統之中，按一定格式分割，如文字檔案或者其他型別的表可以作為外部表。
對外部表的訪問可以通過SQL語句來完成，而不需要先將外部表中的資料裝載進資料庫中。
外部資料表都是隻讀的，因此在外部表不能夠執行DML操作，也不能建立索引。
ANALYZE語句不支援採集外部表的統計資料，應該使用DMBS_STATS包來採集外部表的統計資料。
建立外部表的注意事項
1.需要先建立目錄物件。在建立物件的時候，需要小心，Oracle資料庫系統不會去確認這個目錄是否真的存在。如果在輸入這個目錄物件的時候，不小心把路徑寫錯了，那可能這個外部表仍然可以正常建立，但是卻無法查詢到資料。由於建立目錄物件時，缺乏這種自我檢查的機制，為此在將路徑賦予給這個目錄物件時，需要特別的注意。另外需要注意的是路徑的大小寫。在Windows作業系統中，其路徑是不區分大小寫的。而在Linux作業系統，這個路徑需要區分大小寫。故在不同的作業系統中，建立目錄物件時需要注意這個大小寫的差異。
2.對於作業系統檔案的要求
建立外部表時，必須指定作業系統檔案所使用的分隔符號。並且該分隔符有且只有一個。建立外部表時，不能含有標題列。如果這個標題資訊與外部表的欄位型別不一致（如欄位內容是number資料型別，而標題資訊則是字元型資料，則在查詢時就會出錯）。如果資料型別恰巧一致的話，這個標題資訊Oracle資料庫也會當作普通記錄來對待。

當Oracle資料庫系統訪問這個作業系統檔案的時候，會在這個檔案所在的目錄自動建立一個日誌檔案。無論最後是否訪問成功，這個日誌檔案都會如期建立。檢視這個日誌檔案，可以瞭解資料庫訪問外部表的頻率、是否成功訪問等等。預設情況下，該日誌在與外部表的相同directory下產生。
3.在建立臨時表時的相關限制
對錶中欄位的名稱存在特殊字元的情況下，必須使用英文狀態的下的雙引號將該表列名稱連線起來。如採用”SalseID#”。
對於列名字中特殊符號未採用雙引號括起來時，會導致無法正常查詢資料。
建議不用使用特殊的列標題字元
在建立外部表的時候，並沒有在資料庫中建立表，也不會為外部表分配任何的儲存空間。
建立外部表只是在資料字典中建立了外部表的後設資料，以便對應訪問外部表中的資料，而不在資料庫中儲存外部表的資料。
簡單地說，資料庫儲存的只是與外部檔案的一種對應關係，如欄位與欄位的對應關係。而沒有儲存實際的資料。
由於儲存實際資料，故無法為外部表建立索引，同時在資料使用DML時也不支援對外部表的插入、更新、刪除等操作。
4.刪除外部表或者目錄物件
一般情況下，先刪除外部表，然後再刪除目錄物件，如果目錄物件中有多個表，應刪除所有表之後再刪除目錄物件。
如果在未刪除外部表的情況下，強制刪除了目錄，在查詢到被刪除的外部表時，將收到"物件不存在"的錯誤資訊。
查詢dba_external_locations來獲得當前所有的目錄物件以及相關的外部表，同時會給出這些外部表所對應的作業系統檔案的名字。 如果只是在資料庫層面上刪除外部表，並不會自動刪除作業系統上的外部表檔案。
5.對於作業系統平臺的限制
不同的作業系統對於外部表有不同的解釋和顯示方式
如在Linux作業系統中建立的檔案是分號分隔且每行一條記錄，但該檔案在Windows作業系統上開啟則並非如此。
建議避免不同作業系統以及不同字符集所帶來的影響
建立外部表
使用CREATE TABLE語句的ORGANIZATION EXTENERAL子句來建立外部表。外部表不分配任何盤區，因為僅僅是在資料字典中建立後設資料。
1.外部表的建立語法
createtabletable_name
(col1 datatype1,col2 datatype2,col3 datatype3)
organization exteneral
(.....)
詳細語法可參見筆者的另兩篇文章

Oracle外部表ORACLE_DATAPUMP型別的建立語法詳解：http://czmmiao.iteye.com/blog/1268453

Oracle外部表ORACLE_LOADER型別的建立語法詳解：http://czmmiao.iteye.com/blog/1268157
2.由查詢結果集，使用Oracle_datapump來填充資料來生成外部表
a.建立系統目錄以及Oracle資料目錄名來建立對應關係，同時授予許可權
[oracle@oradb ~]$ mkdir-p/home/oracle/external_tb/data

SQL> create or replace directory dat_dir as '/home/oracle/external_tb/data/';
SQL> alter user scott account unlock identified by scott;
b.建立外部表

SQL>create table ex_tb1 --建立外部表
2 (ename,job,sal,dname) --表列描述，注意未指定資料型別
3 organization external
4 (
5 type oracle_datapump --使用datapump將查詢結果填充到外部表,注,此處由select生成，故不支援

oracle_loader
6 default directory dat_dir --指定外部表的存放目錄
7 location('tb1.exp，tb2.exp'))
8 parallel 2 --按並行方式來填充，這裡的並行度必須與生成的檔案數量一致才能起作用，詳細演算法可

9 as 以參看http://czmmiao.iteye.com/blog/1268453
10 select ename,job,sal,dname --填充使用的原始資料
11 from emp join dept
12 on emp.deptno=dept.deptno
c.驗證外部表
SQL> select * from ex_tb1;
ENAME JOB SAL DNAME
---------- --------- ---------- --------------
SMITH CLERK 800 RESEARCH
ALLEN SALESMAN 1600 SALES
..................................
MILLER CLERK 1300 ACCOUNTING
對於使用上述方式建立的外部表可以將其複製到其他路徑作為外部表的原始資料來生成新的外部表，用於轉移資料。
3.使用SQLLDR提供外部表的定義並建立外部表
關於SQL*Loader的使用請參照：SQL*Loader使用方法
我們使用SQL*Loader和下面的這個控制檔案來生成外部表的定義
$ 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

賦予相應的許可權和建立表

SQL>grant create any directory to scott;
SQL>grant drop any directory to scott;
SQL>create table dept_new
2 (deptno number,dname varchar2(20),loc varchar2(25));
執行sqlldr命令
$ sqlldr scott/tiger control=demo1.ctl external_table=generate_only
EXTERNAL_TABLE引數有以下三個值：
NOT_USED：預設值。
EXECUTE：這個值說明SQLLDR不會生成並執行一個SQLINSERT語句；而是會建立一個外部表，且使用一個批量SQL語句來載入。
GENERATE_ONLY：使SQLLDR並不具體載入任何資料，而只是會生成所執行的SQL DDL和DML語句，並放到它建立的日誌檔案中。
注：DIRECT=TRUE覆蓋EXTENAL_TABLE=GENERATE_ONLY。如果指定了DIRECT=TRUE，則會載入資料，而不會生成外部表。
$ cat demo1.log --檢視sqlldr產生的日誌檔案
SQL*Loader: Release 10.2.0.1.0 - Production on Sun Nov 20 17:45:36 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Control File: demo1.ctl
Data File: demo1.ctl
Bad File: demo1.bad
Discard File: none specified

(Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation: none specified
Path used: External Table

Table DEPT_NEW, loaded from every logical record.
Insert option in effect for this table: INSERT

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
DEPTNO FIRST * , CHARACTER
DNAME NEXT * , CHARACTER
LOC NEXT * , CHARACTER

CREATE DIRECTORY statements needed for files
------------------------------------------------------------------------
CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS '/home/oracle' --建立目錄物件的語句

CREATE TABLE statement for external table:
------------------------------------------------------------------------
CREATE TABLE "SYS_SQLLDR_X_EXT_DEPT_NEW"
(
"DEPTNO" NUMBER(2),
"DNAME" VARCHAR2(14),
"LOC" VARCHAR2(13)
)
ORGANIZATION external
(
TYPE oracle_loader --指定外部表的訪問方式，9i不支援oracle_datapump
DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
ACCESS PARAMETERS --配置外部表引數
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII --記錄以換行為結束
BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'demo1.bad' --存放處理失敗的記錄檔案描述
LOGFILE 'demo1.log_xt' --日誌檔案
READSIZE 1048576 --Oracle讀取輸入資料檔案所用的預設緩衝區,此處為MB,如專用模式則從PGA分配,如共享模式
則從SGA分配

SKIP 6 --跳過的記錄數，因為我們使用了控制檔案，所以前面的控制資訊需要跳過
FIELDS TERMINATED BY "," LDRTRIM --描述欄位的終止符
REJECT ROWS WITH ALL NULL FIELDS --所有為空值的行被跳過並且記錄到bad file.
( --下面是描述外部檔案各個列的定義
"DEPTNO" CHAR(255)
TERMINATED BY ",",
"DNAME" CHAR(255)
TERMINATED BY ",",
"LOC" CHAR(255)
TERMINATED BY ","
)
)
location
(
'demo1.ctl' --描述外部檔案的檔名
)
)REJECT LIMIT UNLIMITED --描述允許的錯誤數，此處為無限制


INSERT statements used to load internal tables:
------------------------------------------------------------------------
INSERT /*+ append */ INTO DEPT_NEW
(
DEPTNO,
DNAME,
LOC
)
SELECT
"DEPTNO",
"DNAME",
"LOC"
FROM "SYS_SQLLDR_X_EXT_DEPT_NEW"

statements to cleanup objects created by previous statements:
------------------------------------------------------------------------
DROP TABLE "SYS_SQLLDR_X_EXT_DEPT_NEW" --用於刪除目錄和外部表的定義資訊
DROP DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000

Run began on Sun Nov 20 17:45:36 2011
Run ended on Sun Nov 20 17:45:37 2011

Elapsed time was: 00:00:00.25
CPU time was: 00:00:00.05
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

$ pwd
/home/oracle/external_tb/data
$ ls
1.dat 2.dat dat_dir:tb_test.exp EMP_NEW_3198.log EMP_NEW_3413.log EX_TB1_3021.log
驗證外部表

SQL> select * from emp_new;
EMP_ID ENAME JOB MGR_ID HIREDATE SALARY COMM DEPT_ID
---------- --------------- ------------ ---------- --------- ---------- ---------- ----------
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 0 30
....................................................................
外部表不能執行DML

SQL> delete from emp_new;
delete from emp_new
*
ERROR at line 1:
ORA-30657: operation not supported on external organized table
oracle外部表的簡單使用：



外部表的含義：

外部表是指不在資料庫中的表，如作業系統上的一個按一定格式分割的文字檔案或者其他型別的表。這個外部表對於Oracle資料庫來說，就好像是一張檢視， 在資料庫中可以像檢視一樣進行查詢等操作。這個檢視允許使用者在外部資料上執行任何的SQL語句，而不需要先將外部表中的資料裝載進資料庫中。不過需要注意是，外部資料表都是隻讀的，不能夠更改。

外部表使用限制：（來源於http://www.examda.com/oracle/zonghe/20101031/141746967.html）

需要先建立目錄物件：在建立外部表之前先要建立一個這個外部表要指向的檔案所在目錄路徑的目錄；

對於作業系統檔案的限制：

其實外部表簡單的說，就是跟作業系統上固定格式的檔案或者表格的一個連線。為了Oracle資料庫系統能夠正確連結外部表，對於外部表的格式就提出 了比較嚴格的要求。如果不符合這些要求的話，資料庫系統就無法正確讀取外部表中的資料。如對於分隔符有比較嚴格的要求。雖然在外部檔案或者表格中，可以使 用多種分隔符，如英文狀態下的逗號或者分號等等。但是有一個限制，即在同一個作業系統檔案中只能夠使用一個分割符號，要麼逗號或者分號等等。因為在建立外 部表時，必須指定作業系統檔案所使用的分隔符號。如果有多種分隔符號的話，資料庫系統將無法識別。

　　另外在外部表格中，不能夠帶有標題資訊。如現在有一張表格，以逗號分隔。而在其第一列資料中有各個列的標題資訊。而資料庫系統在連線這個表的時 候，會將這些標題資訊當作普通的紀錄來對待。即會將這些資訊也顯示在外部表中。為此如果這個標題資訊與外部表的欄位型別不一致（如欄位內容是number 資料型別，而標題資訊則是字元型資料，則在查詢時就會出錯）。如果資料型別恰巧一致的話，這個標題資訊Oracle資料庫也會當作普通記錄來對待。如在建 立外部表的時候，最好確認一下作業系統檔案中是否包含標題資訊。如果有的話，需要刪除。否則的話，可能會出錯。

　　最後需要說明的是，當Oracle資料庫系統訪問這個作業系統檔案的時候，會在這個檔案所在的目錄自動建立一個日誌檔案。無論最後是否訪問成功，這個日誌檔案都會如期建立。檢視這個日誌檔案，可以瞭解資料庫訪問外部表的頻率、是否成功訪問等等。

在建立臨時表時的限制：
在建立臨時表時，也會有不少的限制。如表中欄位的名稱有一些特殊字元的話，那麼這個表列的名稱必須使用英文狀態的下的雙引號連線起來。如採用 “studentno#”。遇到列名字中有特殊符號時，如果不採用雙引號括起來，雖然臨時表可以正常建立，但是在採用的時候會出現錯誤，無法正常查詢數 據。如資料庫系統可能會提醒：“資料庫外掛錯誤”等資訊。為此最好在建立臨時表時不要在列名中使用一些特殊的字元。其實不光光是建立臨時表有這種限制，建 立其他標或者試圖都有類似的限制。

　　其次，這個外部表畢竟與內部表不同。在建立外部表的時候，其實在資料庫中跟本沒有建立表。也就是說，不會為外部表分配任何的儲存空間。建立外部 表只是在資料字典中建立了外部表的後設資料，以便對應訪問外部表中的資料，而不在資料庫中儲存外部表的資料。簡單地說，資料庫儲存的只是與外部檔案的一種對 應關係，如欄位與欄位的對應關係。而沒有儲存實際的資料。為此在表的操作與管理上，就會受到很大的限制。如在外部表上，是不能夠為表建立索引。因為建立索 引就意味著要存在對應的索引記錄。而外部表其實在資料庫不會有儲存。故在外部中是無法建立索引的。如果硬要建立的話，則系統會提示“操作在外部組織表上不 受支援”的錯誤提示。同樣的道理，在資料庫中也不能夠更新外部表中的資料，如插入記錄、刪除記錄或者更新資訊等等。簡而言之，這個外部表對於資料庫來說， 是隻讀的，不可更新。

刪除外部表或者目錄物件：
當外部表不用時，需要及時刪除外部表或者與之對應的目錄物件。不過在刪除這些內容時會有一些限制。這些限制主要是管理上的限制，而不是技術上的限 制。也就是說，Oracle資料庫系統沒有對其進行強制的限制。但是如果資料庫管理員不遵守這些限制的話，可能會出現一些問題。如要先刪除外部表，然後再 刪除目錄物件。有時候一個目錄物件中可能會包含多個外部表。此時必須要確認所有的外部表都不用了，都已經刪除乾淨了，然後才能夠刪除目錄物件。在建立外部 表時，作業系統會判斷一下，與之對應的目錄物件是否已經建立。但是在刪除物件時，系統不會去判斷跟這個目錄物件關聯的外部表是否已經全部刪除。如果目錄對 象刪除了，但是還有外部表存在。此時查詢這個外部表的時候，系統就會提示“物件不存在”的錯誤資訊。所以這個刪除目錄物件時，資料庫系統缺乏一種檢查，此 時只有資料庫管理員在刪除目錄物件時，先手工確認一下這個目錄物件是否存在其他的外部表。

　　要了解這個資訊，則可以通過查詢dba_external_locations。通過查詢這張表，系統會反映當前所有的目錄物件以及相關的外部 表，還會查詢出這些外部表所對應的作業系統檔案的名字。先查詢這張表格，確定要刪除的物件沒有其他關聯的外部表時，再進行刪除。否則的話，需要先確認其他 外部表的可用性。免得因為誤刪除而導致外部表無法正常使用。

對於作業系統平臺的限制：
雖然Oracle資料庫是支援跨平臺的資料庫系統，即同時支援Windows或者Linux等多種作業系統。但是在使用外部表的時候需要注意一個問題，即 在兩個作業系統上文字檔案的儲存方式是不同的。如在Windows作業系統上利用txt檔案建立了一個以逗號作為分隔符的檔案，其一行一條記錄。但是在 Linux作業系統上開啟的話，在其就可能使在同一行中顯示了。故為了資料庫系統能夠正確識別作業系統檔案，最好這個作業系統檔案能夠和Oracle資料 庫系統部署在同一臺伺服器上或者同一種作業系統上。否則的話，很可能因為格式的衝突，而導致資料庫系統無法正確讀取外部檔案中的資料。
外部表的例項：
--建立目錄
create or replace directory dir_bdump as 'D:\oracle\product\10.2.0\admin\fgisdb\bdump';
--查詢目錄
select * from dba_directories;
--查詢外部表的目錄
select * from dba_external_locations;
--查詢外部表
select * from dba_external_tables;
--建立外部表
create table alert_fgisdb ( text varchar2(400) )
organization external (
type oracle_loader
default directory bdump
access parameters (
records delimited by newline
nobadfile
nodiscardfile
nologfile
)
location('alert_fgisdb.log')
)
reject limit unlimited

--通過外部表查詢資料庫的執行資訊


select to_char(last_time,'dd-mon-yyyy hh24:mi') shutdown,
to_char(start_time,'dd-mon-yyyy hh24:mi') startup,
round((start_time-last_time)*24*60,2) mins_down,
round((last_time-lag(start_time)over(order by r)),2)days_up,
case when (lead(r) over (order by r) is null) --lead函式用於取出後N行資料
then round((sysdate-start_time),2)
end days_still_up
from (
select r,
to_date(last_time,'Dy Mon DD HH24:MI:SS YYYY') last_time,
to_date(start_time,'Dy Mon DD HH24:MI:SS YYYY') start_time
from (
select r,
text,
lag(text,1)over(order by r) start_time,--lag函式用於取出前n行資料
lag(text,2)over(order by r) last_time
from (
select rownum r,text
from alert_fgisdb
where text like'____:_:_20_'
or text like'starting oracle instance%'
)
)
where text like 'starting oracle instance%'
)


lag函式語法：lag（欄位，n）

lead函式語法與lag一樣。


--更改拒絕限制
ALTER TABLE alert_fgisdb LIMIT 100;
--更改預設目錄說明
ALTER TABLE alert_fgisdb DIRECTORY DEFAULT DIRECTORY bdump;
--修改訪問引數,如分隔符由","變為"|"
ALTER TABLE alert_fgisdb PARAMETERS ACCESS PARAMETERS (FIELDS TERMINATED BY '|');
--修改檔案位置:
ALTER TABLE alert_fgisdb LOCATION('TC_REG_MNGREGIONCODE.txt');

drop table alert_fgisdb;
--刪除目錄
drop DIRECTORY bdump;
--查詢外部表（找出alert中含有ora的所有記錄）
select * from alert_fgisdb where text like 'ORA-%';


使用外部表解除安裝資料
--準備一個簡單的select語句向這個目錄中解除安裝資料
create table all_objects_unload
organization external
(type oracle_datapump
default directory dir_dp
location('allobjects.dat')) --allobjects.dat檔案在dir_dp目錄下
as select * from all_objects

--將allobjects.dat檔案拷到要載入該表的機器，使用如下語句抽取DDL重建這個表
select dbms_metadata.get_ddl('TABLE','ALL_OBJECTS_UNLOAD') from dual;
--抽取後的DDL語句如下：
CREATE TABLE "GWM"."ALL_OBJECTS_UNLOAD"
( "OWNER" VARCHAR2(30),
"OBJECT_NAME" VARCHAR2(30),
"SUBOBJECT_NAME" VARCHAR2(30),
"OBJECT_ID" NUMBER,
"DATA_OBJECT_ID" NUMBER,
"OBJECT_TYPE" VARCHAR2(19),
"CREATED" DATE,
"LAST_DDL_TIME" DATE,
"TIMESTAMP" VARCHAR2(19),
"STATUS" VARCHAR2(7),
"TEMPORARY" VARCHAR2(1),
"GENERATED" VARCHAR2(1),
"SECONDARY" VARCHAR2(1)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY "DIR_DP"

LOCATION
( 'allobjects.dat'
)
)

--重建該表後，執行如下語句就可以載入這個表的資訊
insert /*+ append */ into some_table select * from all_objects_unload;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29144194/viewspace-1063859/，如需轉載，請註明出處，否則將追究法律責任。

相關文章