【轉】Oracle 外部表

--=================

-- Oracle 外部表

--=================

外部表只能在Oracle 9i 之後來使用。簡單地說，外部表，是指不存在於資料庫中的表。通過向Oracle提供描述外部表的後設資料，我們

可以把一個作業系統檔案當成一個只讀的資料庫表，就像這些資料儲存在一個普通資料庫表中一樣來進行訪問。外部表是對資料庫表的延伸。

一、外部表的特性

位於檔案系統之中，按一定格式分割，如文字檔案或者其他型別的表可以作為外部表。

對外部表的訪問可以通過SQL語句來完成，而不需要先將外部表中的資料裝載進資料庫中。

外部資料表都是隻讀的，因此在外部表不能夠執行DML操作，也不能建立索引。

ANALYZE語句不支援採集外部表的統計資料，應該使用DMBS_STATS包來採集外部表的統計資料。

二、建立外部表的注意事項

1.需要先建立目錄物件

2.對於作業系統檔案的要求

檔案要有固定的格式、不能有標題列、訪問時會自動建立一個日誌檔案

3.在建立臨時表時的相關限制

對錶中欄位的名稱存在特殊字元的情況下，必須使用英文狀態的下的雙引號將該表列名稱連線起來。如採用”SalseID#”。

對於列名字中特殊符號未採用雙引號括起來時，會導致無法正常查詢資料。

建議不用使用特殊的列標題字元

在建立外部表的時候，並沒有在資料庫中建立表，也不會為外部表分配任何的儲存空間。

建立外部表只是在資料字典中建立了外部表的後設資料，以便對應訪問外部表中的資料，而不在資料庫中儲存外部表的資料。

簡單地說，資料庫儲存的只是與外部檔案的一種對應關係，如欄位與欄位的對應關係。而沒有儲存實際的資料。

由於儲存實際資料，故無法為外部表建立索引，同時在資料使用DML時也不支援對外部表的插入、更新、刪除等操作。

4.刪除外部表或者目錄物件

一般情況下，先刪除外部表，然後再刪除目錄物件，如果目錄物件中有多個表，應刪除所有表之後再刪除目錄物件。

如果在未刪除外部表的情況下，強制刪除了目錄，在查詢到被刪除的外部表時，將收到"物件不存在"的錯誤資訊。

查詢dba_external_locations來獲得當前所有的目錄物件以及相關的外部表，同時會給出這些外部表所對應的作業系統檔案的名字。

5.對於作業系統平臺的限制

不同的作業系統對於外部表有不同的解釋和顯示方式

如在Linux作業系統中建立的檔案是分號分隔且每行一條記錄，但該檔案在Windows作業系統上開啟則並非如此。

建議避免不同作業系統以及不同字符集所帶來的影響

三、建立外部表

使用CREATE TABLE語句的ORGANIZATION EXTENERAL子句來建立外部表。外部表不分配任何盤區，因為僅僅是在資料字典中建立後設資料。

1.外部表的建立語法

create table table_name

(col1 datatype1,col2 datatype2 ,col3 datatype3)

organization exteneral

(.....)

2.由查詢結果集，使用Oracle_datapump來填充資料來生成外部表

a.建立系統目錄以及Oracle資料目錄名來建立對應關係，同時授予許可權

[oracle@oradb ~]$ mkdir -p /home/oracle/external_tb/data

sys@ORCL> create or replace directory dat_dir as '/home/oracle/external_tb/data/';

sys@ORCL> grant read,write on directory dat_dir to scott;

b.建立外部表

scott@ORCL> 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 )

9 parallel --按並行方式來填充

10 as

11 select ename,job,sal,dname -填充使用的原始資料

12 from emp join dept

13 on emp.deptno=dept.deptno;

c.--驗證外部表

scott@ORCL> select * from ex_tb1;

ENAME JOB SAL DNAME

---------- --------- ---------- --------------

SMITH CLERK 800 RESEARCH

ALLEN SALESMAN 1600 SALES

WARD SALESMAN 1250 SALES

JONES MANAGER 2975 RESEARCH

..........

對於使用上述方式建立的外部表可以將其複製到其他路徑作為外部表的原始資料來生成新的外部表，用於轉移資料。

3.使用SQLLDR提供外部表的定義並建立外部表

關於SQL*Loader的使用請參照：SQL*Loader使用方法

我們使用SQL*Loader和下面的這個控制檔案來生成外部表的定義

[oracle@oradb ~]$ cat demo1.ctl

LOAD DATA

INFILE *

INTO TABLE DEPT_NEW

FIELDS TERMINATED BY ','

(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 引數有以下三個值：

NOT_USED：預設值。

EXECUTE：這個值說明SQLLDR不會生成並執行一個SQL INSERT語句；而是會建立一個外部表，且使用一個批量SQL語句來載入。

GENERATE_ONLY：使SQLLDR 並不具體載入任何資料，而只是會生成所執行的SQL DDL 和DML 語句，並放到它建立的日誌檔案中。

注：DIRECT=TRUE 覆蓋EXTENAL_TABLE=GENERATE_ONLY。如果指定了DIRECT=TRUE，則會載入資料，而不會生成外部表。

[oracle@oradb ~]$ cat demo1.log --檢視sqlldr產生的日誌檔案

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(20),

"LOC" VARCHAR2(20)

)

ORGANIZATION external --該子句表明是一個外部表 heap 對應普通表，index 對應iot，external 對應外部表

(

TYPE oracle_loader --說明外部檔案訪問方式：oracle_loader或oracle_datapump(9i不支援)

DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 --指定外部檔案的預設目錄

ACCESS PARAMETERS --這個訪問引數有些類似於sqlldr中控制檔案中的描述資訊

( --系統根據這些描述資訊來生成外部表的格式

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: --用於將資料填充到表，使用append方式

------------------------------------------------------------------------

INSERT /*+ append */ INTO DEPT_NEW

(

DEPTNO,

DNAME,

LOC

)

SELECT

"DEPTNO",

"DNAME",

"LOC"

FROM "SYS_SQLLDR_X_EXT_DEPT"

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

------------------------------------------------------------------------

DROP TABLE "SYS_SQLLDR_X_EXT_DEPT_NEW"

DROP DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000

-----------------------------------------------------------------------------------------------------------------------

sys@ORCL> grant create any directory to scott;

sys@ORCL> grant drop any directory to scott;

scott@ORCL> create table dept_new

2 (deptno number,dname varchar2(20),loc varchar2(25));

scott@ORCL> select * from dept_new;

no rows selected

[oracle@oradb ~]$ sqlldr scott/tiger control=demo1.ctl external_table=execute

scott@ORCL> select * from dept_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.繼續使用前面建立的目錄/home/oracle/external_tb/data 來存放資料檔案：

sys@ORCL> select * from dba_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.建立外部表

scott@ORCL> get /u01/bk/scripts/tb.emp_new

1 create table emp_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 organization external

13 (

14 type oracle_loader

15 default directory dat_dir

16 access parameters

17 (

18 records delimited by newline

19 fields terminated by ','

20 )

21 location

22 ('1.dat','2.dat')

23* );

scott@ORCL> start /u01/bk/scripts/tb.emp_new

d.驗證外部表　　

scott@ORCL> select * from emp_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> delete from emp_new where ename='SMITH'; --外部表不能執行DML

delete from emp_new where ename='SMITH'

*

ERROR at line 1:

ORA-30657: operation not supported on external organized table

scott@ORCL> insert into emp_new(emp_id,ename) select 8888,'Robinson' from dual;

insert into emp_new(emp_id,ename) select 8888,'Robinson' from dual

*

ERROR at line 1:

ORA-30657: operation not supported on external organized table

　　 e.獲得外部表的有關資訊：

scott@ORCL> col access_parameters format a35

scott@ORCL> select owner,table_name,type_name,default_directory_name,access_parameters

2 from dba_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 delimited by newline

fields terminated by ','

SCOTT EMP_PUMP ORACLE_DATAPUMP DAT_DIR records delimited by newline

fields terminated by ','

　　 f.獲得平面檔案的位置，使用如下的查詢：

scott@ORCL> select * from dba_external_locations order by table_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.外部表定義的進一步分析

CREATE TABLE external_table

(

COL01 VARCHAR2(100),

COL02 NUMBER,

......

)

ORGANIZATION EXTERNAL

(

TYPE ORACLE_LOADER

DEFAULT DIRECTORY "XXX"

ACCESS PARAMETERS

(

RECORDS DELIMITED BY 0X'0A'

SKIP 1

BADFILE 'bad.txt'

FIELDS TERMINATED BY ','

OPTIONALLY ENCLOSED BY '"'

LRTRIM MISSING FIELD VALUES ARE NULL

REJECT ROWS WITH ALL NULL FIELDS

)

LOCATION

("CJ_DIR":'data.txt')

)REJECT LIMIT UNLIMITED;

外部表定義的幾個重點

a.ORGANIZATION EXTERNAL 關鍵字，必須要有。以表明定義的表為外部表。

b.重要引數外部表的型別

ORACLE_LOADER ：定義外部表的預設方式，只能只讀方式實現文字資料的裝載。

ORACLE_DATAPUMP ：支援對資料的裝載與解除安裝，資料檔案必須為二進位制dump檔案。可以從外部表提取資料裝載到內部表，也

可以從內部表解除安裝資料作為二進位制檔案填充到外部表。

c.DEFAULT DIRECTORY ：預設的目錄指明瞭外部檔案所在的路徑

d.LOCATION ：定義了外部表的位置

f.ACCESS PARAMETERS ：描述如何對外部表進行訪問

RECORDS關鍵字後定義如何識別資料行

DELIMITED BY 'XXX' ——換行符，常用newline定義換行，並指明字符集。對於特殊的字元則需要單獨定義，

如特殊符號，可以使用OX'十六位值'，例如tab(/t)的十六位是9，則DELIMITED BY 0X'09'；

cr(/r)的十六位是d，那麼就是DELIMITED BY 0X'0D'。

SKIP X ——跳過X行資料，有些檔案中第一行是列名，需要跳過第一行，則使用SKIP 1。

FIELDS關鍵字後定義如何識別欄位，常用的如下：

FIELDS：TERMINATED BY 'x' ——欄位分割符。

ENCLOSED BY 'x' ——欄位引用符，包含在此符號內的資料都當成一個欄位。

例如一行資料格式如："abc","a""b,""c,"。使用引數TERMINATED BY ',' ENCLOSED BY '"'後，系統會讀到兩個欄位，

第一個欄位的值是abc，第二個欄位值是a"b,"c,。

LRTRIM ——刪除首尾空白字元。

MISSING FIELD VALUES ARE NULL ——某些欄位空缺值都設為NULL。

對於欄位長度和分割符不確定且準備用作外部表檔案，可以使用UltraEdit、Editplus等來進行分析測試，如果檔案較

大，則需要考慮將檔案分割成小檔案並從中提取資料進行測試。

外部表對錯誤的處理

REJECT LIMIT UNLIMITED

在建立外部表時最後加入LIMIT子句，表示可以允許錯誤的發生個數。預設值為零。設定為UNLIMITED則錯誤不受限制

BADFILE 和NOBADFILE 子句

用於指定將捕獲到的轉換錯誤存放到哪個檔案。如果指定了NOBADFILE則表示忽略轉換期間的錯誤

如果未指定該引數，則系統自動在源目錄下生成與外部表同名的.BAD檔案

BADFILE記錄本次操作的結果，下次將會被覆蓋

LOGFILE 和NOLOGFILE 子句

同樣在accessparameters中加入LOGFILE 'LOG_FILE.log'子句，則所有Oracle的錯誤資訊放入'LOG_FILE.log'中

而NOLOGFILE子句則表示不記錄錯誤資訊到log中，如忽略該子句，系統自動在源目錄下生成與外部表同名的.LOG檔案

注意以下幾個常見的問題

1.外部表經常遇到BUFFER不足的情況，因此儘可能的增大READ SIZE

2.換行符不對產生的問題。在不同的作業系統中換行符的表示方法不一樣，碰到錯誤日誌提示如是換行符問題，可以使用

UltraEdit開啟，直接看十六進位制

3.特定行報錯時，檢視帶有"BAD"的日誌檔案,其中儲存了出錯的資料,用記事本開啟看看那裡出錯,是否存在於外部表定義相沖突

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

【轉】Oracle 外部表

相關文章