oracle外部表的測試學習 （轉）

一、外部表的特性
位於檔案系統之中，按一定格式分割，如文字檔案或者其他型別的表可以作為外部表。對外部表的訪問可以透過SQL語句來完成，而不需要先將外部表中的資料裝載進資料庫中。外部資料表都是隻讀的，因此在外部表不能夠執行DML操作，也不能建立索引。ANALYZE語句不支援採集外部表的統計資料，應該使用DMBS_STATS包來採集外部表的統計資料。

二、建立外部表的注意事項
1.需要先建立目錄物件
2.對於作業系統檔案的要求
檔案要有固定的格式、不能有標題列、訪問時會自動建立一個日誌檔案
3.在建立臨時表時的相關限制
對錶中欄位的名稱存在特殊字元的情況下，必須使用英文狀態的下的雙引號將該表列名稱連線起來。如採用”SalseID#”。對於列名字中特殊符號未採用雙引號括起來時，會導致無法正常查詢資料。建議不用使用特殊的列標題字元。
在建立外部表的時候，並沒有在資料庫中建立表，也不會為外部表分配任何的儲存空間。建立外部表只是在資料字典中建立了外部表的後設資料，以便對應訪問外部表中的資料，而不在資料庫中儲存外部表的資料。簡單地說，資料庫儲存的只是與外部檔案的一種對應關係，如欄位與欄位的對應關係。而沒有儲存實際的資料。由於儲存實際資料，故無法為外部表建立索引，同時在資料使用DML時也不支援對外部表的插入、更新、刪除等操作。
4.刪除外部表或者目錄物件
一般情況下，先刪除外部表，然後再刪除目錄物件，如果目錄物件中有多個表，應刪除所有表之後再刪除目錄物件。如果在未刪除外部表的情況下，強制刪除了目錄，在查詢到被刪除的外部表時，將收到"物件不存在"的錯誤資訊。查詢dba_external_locations來獲得當前所有的目錄物件以及相關的外部表，同時會給出這些外部表所對應的作業系統檔案的名字。
5.對於作業系統平臺的限制
不同的作業系統對於外部表有不同的解釋和顯示方式如在Linux作業系統中建立的檔案是分號分隔且每行一條記錄，但該檔案在Windows作業系統上開啟則並非如此。建議避免不同作業系統以及不同字符集所帶來的影響

三、外部表的建立
這裡使用一個簡單常用的alterlog建立外部表的例項來作為測試
首先建立一個目錄
create or replace directory dir_alert as '/opt/oracle/admin/test/bdump';
grant read,write on directory dir_alert to yuzh;

建立外部表
create table alert_log
( text varchar2(400) )
organization external
(
 type oracle_loader
default directory dir_alert
access parameters (
                  records delimited by newline
                  nobadfile
                  nodiscardfile
                  nologfile
                  )
location('alert_test.log')
)
reject limit unlimited;
因為沒用分割符號，所以檔案中的每一行對應外部表的每一行，所以這裡建立外部表只有一個欄位。資料的預設排序和檔案中的記錄行一致，先進的序號在前，後進的序號在後。

再來個使用分隔符的表
create table test_e
(
     cl1 number(10),
     cl2 varchar2(20)
)
 organization external
(
   type oracle_loader
 default directory dir_alert
 access parameters
 (
     records delimited by newline
     fields terminated by ','
  )
location
  ('text1.txt','text2.txt')
) reject limit unlimited;

自己去建立個兩個txt檔案寫條記錄進去，查詢一下
SQL> select * from test_e;
 
        CL1 CL2
----------- --------------------
    1111111 aaaaaa
     222222 bbbbbb
    
這裡注意的以上例子都是使用的type oracle_loader

其實還有一種type oracle_datapump
這個算是建立一個外部檔案和外部表了，一般來說導資料時候用用不錯的，來個測試用例
create table test_d
organization external
(
  type oracle_datapump
  default directory dir_alert
  location('text3.txt','text4.txt')
)
parallel
as select * from all_objects

執行完畢後去系統目錄下看到3和4的檔案，然後把這兩個檔案複製到別的機器上，再透過建立外部表的模式把相應詩句倒動到其他表中即可。
查詢一下：
SQL> select * from test_d where rownum<10;
 
OWNER                          OBJECT_NAME                    SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE         CREATED     LAST_DDL_TIME TIMESTAMP           STATUS  TEMPORARY GENERATED SECONDARY
------------------------------ ------------------------------ ------------------------------ ---------- -------------- ------------------- ----------- ------------- ------------------- ------- --------- --------- ---------
PUBLIC                         GV$RESUMABLE                                                        2081                SYNONYM             2011-7-28 1 2011-7-28 11: 2011-07-28:11:32:01 VALID   N         N         N
PUBLIC                         GV$TIMEZONE_NAMES                                                   2083                SYNONYM             2011-7-28 1 2011-7-28 11: 2011-07-28:11:32:01 VALID   N         N         N
PUBLIC                         GV$TIMEZONE_FILE                                                    2085                SYNONYM             2011-7-28 1 2011-7-28 11: 2011-07-28:11:32:01 VALID   N         N         N
PUBLIC                         GV$ENQUEUE_STAT                                                     2087                SYNONYM             2011-7-28 1 2011-7-28 11: 2011-07-28:11:32:01 VALID   N         N         N
PUBLIC                         GV$ENQUEUE_STATISTICS                                               2089                SYNONYM             2011-7-28 1 2011-7-28 11: 2011-07-28:11:32:01 VALID   N         N         N
PUBLIC                         GV$LOCK_TYPE                                                        2091                SYNONYM             2011-7-28 1 2011-7-28 11: 2011-07-28:11:32:01 VALID   N         N         N
PUBLIC                         GV$RMAN_CONFIGURATION                                               2093                SYNONYM             2011-7-28 1 2011-7-28 11: 2011-07-28:11:32:01 VALID   N         N         N
PUBLIC                         GV$VPD_POLICY                                                       2095                SYNONYM             2011-7-28 1 2011-7-28 11: 2011-07-28:11:32:01 VALID   N         N         N
PUBLIC                         V$VPD_POLICY                                                        2097                SYNONYM             2011-7-28 1 2011-7-28 11: 2011-07-28:11:32:01 VALID   N         N         N
 
9 rows selected

資料太多，顯示幾條意思意思就行了，呵呵

四、外部表的使用
--更改拒絕限制
ALTER TABLE alert_log LIMIT 100;
--更改預設目錄說明
ALTER TABLE alert_log DIRECTORY DEFAULT DIRECTORY bdump;
--修改訪問引數,如分隔符由","變為"|"
ALTER TABLE alert_log PARAMETERS ACCESS PARAMETERS (FIELDS TERMINATED BY '|');
--修改檔案位置:
ALTER TABLE alert_log LOCATION('aaa.txt');
--刪除表
drop table alert_log ;
--刪除目錄
drop DIRECTORY bdump;

一個簡單的查詢檔案最後500行中是否有ora的錯誤（注：這個資料量少的時候速度不慢，但是大資料量時就不太好說了）
select *
  from (select rownum as rno, text, (select count(*) from alert_log) allnum
          from alert_log)
 where rno > allnum - 500
   and lower(text) like '%ora-%'

還有這種使用偏移量的函式來實現取前後多少行記錄的語句（不過我覺得不是太靠譜，對預設值的唯一性要求高，效率我也不知道快不快）
取前10條（我取預設值0）
select text
  from (select text, lag(rno, 10, 0) over(order by rno) dd
          from (select rownum as rno, text from alert_log))
 where dd = '0'
   and lower(text) like '%ora-%';
取後十條
select text
  from (select text, lead(rno, 10, 0) over(order by rno) dd
          from (select rownum as rno, text from alert_log))
 where dd = '0'
   and lower(text) like '%ora-%';

這裡簡單介紹下偏移量函式
lag函式就是把rno的值往後移動n位，這樣移動後函式值的結果就是n前的都補了預設值，n後的函式結果就是往前數n位的rno值。
lead函式也是一樣的，只是反過來罷了。
 
五、其他說明  

相關的系統檢視
select * from DBA_EXTERNAL_TABLES;
select * from DBA_EXTERNAL_LOCATIONS;

外部表定義的幾個重點
a.ORGANIZATIONEXTERNAL關鍵字，必須要有。以表明定義的表為外部表。
b.重要引數外部表的型別
ORACLE_LOADER：定義外部表的預設方式，只能只讀方式實現文字資料的裝載。
ORACLE_DATAPUMP：支援對資料的裝載與解除安裝，資料檔案必須為二進位制dump檔案。可以從外部表提取資料裝載到內部表，也可以從內部表解除安裝資料作為二進位制檔案填充到外部表。
c.DEFAULTDIRECTORY：預設的目錄指明瞭外部檔案所在的路徑
d.LOCATION：定義了外部表的位置
f.ACCESS PARAMETERS：描述如何對外部表進行訪問
RECORDS關鍵字後定義如何識別資料行 
DELIMITEDBY'XXX'——換行符，常用newline定義換行，並指明字符集。對於特殊的字元則需要單獨定義，如特殊符號，可以使用OX'十六位值'，例如tab(/t)的十六位是9，則DELIMITEDBY0X'09'；cr(/r)的十六位是d，那麼就是DELIMITEDBY0X'0D'。 SKIP X ——跳過X行資料，有些檔案中第一行是列名，需要跳過第一行，則使用SKIP 1。
FIELDS關鍵字後定義如何識別欄位，常用的如下：
FIELDS：TERMINATEDBY'x'——欄位分割符。
ENCLOSEDBY'x'——欄位引用符，包含在此符號內的資料都當成一個欄位。例如一行資料格式如："abc","a""b,""c,"。使用引數TERMINATEDBY','ENCLOSEDBY'"'後，系統會讀到兩個欄位，第一個欄位的值是abc，第二個欄位值是a"b,"c,。
LRTRIM ——刪除首尾空白字元。
MISSING FIELDVALUESARENULL——某些欄位空缺值都設為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不足的情況，因此儘可能的增大READSIZE
2.換行符不對產生的問題。在不同的作業系統中換行符的表示方法不一樣，碰到錯誤日誌提示如是換行符問題，可以使用UltraEdit開啟，直接看十六進位制
3.特定行報錯時，檢視帶有"BAD"的日誌檔案,其中儲存了出錯的資料,用記事本開啟看看那裡出錯,是否存在於外部表定義相沖突

轉自：http://blog.csdn.net/yuzhenhuan01/article/details/6688976