--=================
-- 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和下面的這個控制檔案來生成外部表的定義
[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)
"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
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
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
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"的日志檔案,其中儲存了出錯的資料,用記事本打開看看那裡出錯,是否存在于外部表定義相沖突
>
&
<a href="http://blog.itpub.net/22578826/viewspace-703470"></a>
<a href="http://www.cnblogs.com/lanzi/archive/2010/12/28/1918755.html"></a>
<a href="http://blog.csdn.net/leshami/article/details/6078481"></a>
<a href="http://blog.itpub.net/26736162/abstract/1/"></a> <a href="http://www.cnblogs.com/lhrbest"></a> <a href="http://blog.itpub.net/26736162/viewspace-1624453/"></a> <a href="http://blog.itpub.net/26736162/viewspace-2134706/"></a>Oracle 外部表 Oracle 外部表 <a target="_blank" href="http://wpa.qq.com/msgrd?v=3&uin=646634621&site=qq&menu=yes"></a>Oracle 外部表 Oracle 外部表 Oracle 外部表 Oracle 外部表 Oracle 外部表 Oracle 外部表 Oracle 外部表 Oracle 外部表