Oracle外部表格詳解(轉載)

標籤：

（外部表格建立主要注意建立目錄存取權限問題、目錄路徑格式無空格等不相關字元，即必須是當前表訪問使用者可以訪問；關於表中行數的限制問題，如果不加限制注意添加reject limit unlimited；表中資料格式與建立表時access parameters中的定義需保持同步，適當用skip=1） 外部表格概述

外部表格只能在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資料目錄名來建立對應關係，同時授予許可權
[[email protected] ~]$ 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
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         

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

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

建立外部表格

create table emp_new
   (
       emp_id number(4),
       ename varchar2(15),
       job varchar2(12) ,
       mgr_id number(4) ,
       hiredate date,
       salary number(8),
       comm number(8),
       dept_id number(2)
   )
   organization external
   (
       type oracle_loader
       default directory dat_dir
       access parameters
     (
         records delimited by newline
         fields terminated by ‘,‘
     )
   location
       (‘1.dat‘,‘2.dat‘)
  );
驗證外部表格 

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
查看外部表格資訊

SQL>select owner,table_name,type_name,default_directory_name,access_parameters
       2 from dba_external_tables;

獲得一般檔案的位置，使用如下的查詢：

SQL>select * from dba_external_locations order by table_name; 
外部表格定義的幾個重點 
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，則DELIMITEDBY0X‘09‘；
cr(/r)的十六位是d，那麼就是DELIMITEDBY0X‘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子句
同樣在access parameters中加入LOGFILE ‘LOG_FILE.log‘子句，則所有Oracle的錯誤資訊放入‘LOG_FILE.log‘中
而NOLOGFILE子句則表示不記錄錯誤資訊到log中，如忽略該子句，系統自動在來源目錄下產生與外部表格同名的.LOG檔案
注意以下幾個常見的問題
1.外部表格經常遇到BUFFER不足的情況，因此儘可能的增大READSIZE
2.分行符號不對產生的問題。在不同的作業系統中分行符號的表示方法不一樣，碰到錯誤記錄檔提示如是分行符號問題，可以使用
UltraEdit開啟，直接看十六進位
3.特定行報錯時，查看帶有"BAD"的記錄檔,其中儲存了出錯的資料,用記事本開啟看看那裡出錯,是否存在於外部表格定義相衝突

外部表格的局限性 
１.SQLLDR可以指定多少提交一次，即ROWS=?, 外部表格卻沒有，這對於大資料量的匯入有些不方例。
2.sqlldr errors表示允許錯誤的行數，外部表格用REJECT LIMIT UNLIMITED，這個功能上基本相同。
3.外部表格的列不能指定為not nullable,這樣就很難拒絕某列為空白值的記錄。
4.外部表格不能使用continueif ，如果記錄有換行的就比較難處理。

 

參考至:http://space.itpub.net/22578826/viewspace-703470

            http://web.njit.edu/info/oracle/DOC/server.102/b14215/et_dp_driver.htm#g1017944

            http://web.njit.edu/info/oracle/DOC/server.102/b14215/et_params.htm#g1031955

            http://www.examda.com/oracle/zhonghe/20090817/091840581.html

           http://news.newhua.com/news1/program_database/2010/71/1071152247EKHJED8IA04B6DIA4HA3GGJ4EJ3FEE7896H215DJ8B1HI.html

本文原創，轉載請註明出處、作者

Oracle外部表格詳解(轉載)