Oracle外部表

可以像對其他表一樣，使用SELECT語句查詢外部表，但不能對外部表執行DML操作。這是因為它不是以段的形式存在於資料庫中，而是以資料字典構造存在，指向外部的作業系統檔案。外部表的作業系統檔案透過Oracle目錄物件定位。

 

1、目錄物件

 

Oracle目錄是指向物理路徑的資料庫物件。任何使用者要建立目錄，必須獲得CREATE DIRECTORY許可權。目錄建立後，必須授予使用目錄的Oracle資料庫使用者讀寫目錄的許可權。建立目錄時，Oracle不會驗證目錄路徑是否真的存在。目錄不是模式物件，不管誰建立的，都為SYS所有，因此普通使用者儘管可以建立目錄，甚至可以授權目錄給其他使用者訪問，但卻無權刪除目錄。要檢視有關目錄的資訊，可以查詢DBA_DIRECTORIES或ALL_DIRECTORIES檢視。

 

以下一組語句授權SCOTT使用者建立目錄，並由SCOTT使用者授權給其他使用者使用，由SYS負責刪除

conn / as sysdba

已連線。

 

grant create any directory to scott;

授權成功。

 

conn scott/tiger

已連線。

 

create or replace directory scott_dir as 'd:\scott';

目錄已建立。

 

grant read, write on directory scott_dir to public;

授權成功。

 

select * from all_directories where directory_name='SCOTT_DIR';

 

OWNER        DIRECTORY_NAME     DIRECTORY_PATH

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

SYS           SCOTT_DIR          d:\scott

 

drop directory scott_dir;

 

ORA-01031: 許可權不足

 

conn / as sysdba

已連線。

 

drop directory scott_dir;

目錄已刪除。

 

2、建立和使用外部表

 

外部表不再需要使用SQL*Loader將表讀入資料庫，這在一些饋送系統中會節省大量開支。要建立外部表，使用CREATE TABLE命令以及ORGANIZATION EXTERNAL關鍵字，這告訴Oracle表不以段的形式存在，此後指定作業系統檔案的位置和佈局。而這個指定可以透過SQL*Loader命令列自動生成。以下例子說明了外部表的建立方法。

 

1）用rmes使用者登入mes資料庫建立本例使用的表

create table r_lln_sn_t(id number, sn varchar2(100));

表已建立。

 

2）建立外部資料檔案sn.txt，包含內容如下

1,215# 3QD 500 025 A     ##654272491#010816  *215 5CTHN000014*=

2,215# 3QD 500 025 A     ##654272491#010816  *215 5CTHN000025*=

3,215# 3QD 500 025 A     ##654272491#010816  *215 5CTHN000036*=

4,215# 3QD 500 025 A     ##654272491#010816  *215 5CTHN000047*=

5,215# 3QD 500 025 A     ##654272491#010816  *215 5CTHN000058*=

 

3）建立控制檔案sn.ctl，設定如下

load data

infile 'sn.txt'

badfile 'sn.bad'

truncate

into table r_lln_sn_t

fields terminated by ','

trailing nullcols

(id,sn)

此控制檔案將在執行插入操作前截斷目標檔案。

 

4）用SQL*Loader生成建立外部表的語句，即是在sqlldr命令列指定external_table子句

sqlldr rmes/rmes control=sn.ctl external_table=generate_only

 

這將在日誌檔案sn.log中生成一條CREATE TABLE語句

CREATE TABLE "SYS_SQLLDR_X_EXT_R_LLN_SN_T"

(

  "ID" NUMBER,

  "SN" VARCHAR2(100)

)

ORGANIZATION external

(

  TYPE oracle_loader

  DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000

  ACCESS PARAMETERS

  (

    RECORDS DELIMITED BY NEWLINE CHARACTERSET ZHS16GBK

    BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'sn.bad'

    LOGFILE 'sn.log_xt'

    READSIZE 1048576

    FIELDS TERMINATED BY "," LDRTRIM

    MISSING FIELD VALUES ARE NULL

    REJECT ROWS WITH ALL NULL FIELDS

    (

      "ID" CHAR(255)

        TERMINATED BY ",",

      "SN" CHAR(255)

        TERMINATED BY ","

    )

  )

  location

  (

    'sn.txt'

  )

)REJECT LIMIT UNLIMITED;

 

5）將其中加粗標註的表名更換為自己想要的名稱，將目錄更改為自己建立的目錄，並保證sn.txt檔案在指定的目錄中：

CREATE TABLE "X_R_LLN_SN_T"

(

  "ID" NUMBER,

  "SN" VARCHAR2(100)

)

ORGANIZATION external

(

  TYPE oracle_loader

  DEFAULT DIRECTORY MY_DIR

  ACCESS PARAMETERS

  (

    RECORDS DELIMITED BY NEWLINE CHARACTERSET ZHS16GBK

    BADFILE 'MY_DIR':'sn.bad'

    LOGFILE 'sn.log_xt'

    READSIZE 1048576

    FIELDS TERMINATED BY "," LDRTRIM

    MISSING FIELD VALUES ARE NULL

    REJECT ROWS WITH ALL NULL FIELDS

    (

      "ID" CHAR(255)

        TERMINATED BY ",",

      "SN" CHAR(255)

        TERMINATED BY ","

    )

  )

  location

  (

    'sn.txt'

  )

)REJECT LIMIT UNLIMITED;

 

6）建立好目錄

create directory my_dir as 'd:\';

目錄已建立。

 

7）執行步驟5建立的建表語句建立外部表。

 

8）使用幾條SELECT語句查詢外部表，並嘗試DML操作，可以看到，系統為每個SELECT生成了日誌記錄，日誌檔名形如sn.log_xt，並且不允許執行DML操作。

 

3、使用資料泵引擎建立外部表

 

另一種建立外部表的方法是使用資料泵引擎，將資料庫中正常的表的資料儲存到作業系統的二進位制檔案中，二進位制檔案與透過expdp匯出的檔案類似。可以將二進位制檔案轉移到其它資料庫所在的作業系統平臺上，然後在其資料庫中建立和讀取外部表。這個方法也給不同平臺之間資料庫表資料的遷移提供了一種方法。

 

如下基於當前資料庫中的表建立一個外部表，透過資料泵引擎生成dmp檔案，如果要生成的dmp檔案不止一個，則應當指定使用parallel

create table rmes.r_recent_code_ext(recent_id, code_id, value1, value2, value3, value4, value5, recent_no, code_date, plant_id)

organization external

(

type oracle_datapump

default directory my_dir

location('recent_code_1.dmp', 'recent_code_2.dmp')

)

parallel

as select * from rmes.r_recent_code_t;