ORACLE外部表的應用執行個體

Oracle 9i 的一項新特性就是 External Table，它就象通常的資料庫表一樣，擁有字段和資料類型限制，并且可以查詢，但是表中的資料卻不存儲在資料庫中，而是在與資料庫相關聯的普通外部檔案裡。當你查詢 External Table 時，Oracle 将解析該檔案并傳回符合條件的資料，就象該資料存儲在資料庫表中一樣。

具體的定義可以參見《 Oracle 概念手冊》，以下的幾點需要注意：

<一>:外部表的描述:

> 建立的文法類似于： "CREATE TABLE ... ORGANIZATION EXTERNAL"

> 資料在資料庫的外部組織，是作業系統檔案。

> 作業系統檔案在資料庫中的标志是通過一個邏輯目錄來映射的。

> 資料是隻讀的。（外部表相當于一個隻讀的虛表）

> 不可以在上面運作任何 DML 操作，不可以建立索引。

> 可以查詢操作和連接配接。可以并行操作。

例子:

假如有如下兩個資料檔案:

1: 資料檔案的格式

bjuser.csv檔案:

20080629,修改,1301110022,鄒雪輝,01110022

20080629,修改,1302050023,王曉斌,02050023

20080629,修改,1306060130,邵靜,06060130

20080629,修改,1304020386,張晉,04020386

20080629,修改,1301070082,許征,01070082

2:建立目錄,并進行授權;

sql> create or replace directory out_tabdir as '/oradata';

sql>grant read,write on directory out_tabdir to users;

注意：建立完畢邏輯目錄之後要把平面檔案拷貝到該目錄下，另外還要注意檔案名字不要寫錯。

3:建立外部表:

Create table bjuser

(yyyymm varchar2(8),

pro_no varchar2(50),

user_id varchar2(20),

user_nm varchar2(20),

user_no varchar2(20)

)

ORGANIZATION EXTERNAL

(

TYPE ORACLE_LOADER

DEFAULT DIRECTORY out_tabdir

ACCESS Parameters

(

RECORDS DELIMITED BY NEWLINE

badfile 'bad_bjuser.txt'   

LOGFILE 'log_bjuser.txt'

FIELDS TERMINATED BY ','

MISSING FIELD VALUES ARE NULL

(yyyymm,pro_no,user_id,user_nm,user_no)

)

LOCATION('bjuser.csv')

)reject limit unlimited

表建立完成.當然也可以導入一個檔案

4:進行SELECT 操作看是否正确;

SQL>select * from bjuser

結果如下:

SQL> select * from bjuser;

YYYYMM   PRO_NO                                             USER_ID              USER_NM              USER_NO

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

20080629 修改                                               1301110022           鄒雪輝               01110022

20080629 修改                                               1302050023           王曉斌               02050023

20080629 修改                                               1306060130           邵靜                 06060130

20080629 修改                                               1304020386           張晉                 04020386

20080629 修改                                               1301070082           許征                 01070082

20080629 修改  

<二>: 如何得到外部表的有關資訊:

SQL> DESC DBA_EXTERNAL_TABLES;

Name Type Nullable

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

OWNER VARCHAR2(30)

TABLE_NAME VARCHAR2(30)

TYPE_OWNER CHAR(3) Y

TYPE_NAME VARCHAR2(30)

DEFAULT_DIRECTORY_OWNER CHAR(3) Y

DEFAULT_DIRECTORY_NAME VARCHAR2(30)

REJECT_LIMIT VARCHAR2(40) Y

ACCESS_TYPE VARCHAR2(7) Y

ACCESS_PARAMETERS VARCHAR2(4000) Y

SQL>SELECT OWNER,TABLE_NAME,DEFAULT_DIRECTORY_NAME,ACCESS_PARAMETERSFRFROM

DBA_EXTERNAL_TABLES;

可以得到外部表的相關資訊;

<三>:如何得到外部路徑的資訊:

SQL> desc DBA_EXTERNAL_LOCATIONS;

得到該表結構:

Name Type Nullable

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

OWNER VARCHAR2(30)

TABLE_NAME VARCHAR2(30)

LOCATION VARCHAR2(4000) Y

DIRECTORY_OWNER CHAR(3) Y

DIRECTORY_NAME VARCHAR2(30) Y

SQL> select * from DBA_EXTERNAL_LOCATIONS;

<四> 解除安裝外部表

drop table bjuser

<五> 修改外部表

更改拒絕限制

ALTER TABLE 外部表 LIMIT 100;

更改預設目錄說明

ALTER TABLE 外部表 DIRECTORY DEFAULT DIRECTORY 新目錄路徑;

修改通路參數,如分隔符由","變為"|"

ALTER TABLE 外部表 PARAMETERS ACCESS PARAMETERS (FIELDS TERMINATED BY '|');

修改檔案位置:

ALTER TABLE 外部表   LOCATION('TC_REG_MNGREGIONCODE.txt');

<六>sqlldr生成外部表語句

在Oracle 9i中，sqlldr增加了一個新的參數external_table。通過這個參數的generate_only選項，可以生成完整的外部表建立語句。

現在有一個sqlldr控制檔案，内容如下：

load data

infile 'd:/sqldr/test.txt'

badfile 'd:/sqldr/test.bad'

discardfile 'd:/sqldr/test.dis'

append into table test

fields terminated by X'09'

trailing nullcols

(

id,

name

)

利用如下指令行生成完整的外部表建立語句：

C:/>sqlldr test/[email protected] control=d:/sqldr/test.ctl external_table=generate_only

在c：/根目錄下找到test.log檔案，這個檔案包括了非常詳細的内容，找到相應部分

用于外部表的 CREATE TABLE 語句:

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

CREATE TABLE "SYS_SQLLDR_X_EXT_TEST"

(

"ID" NUMBER(38),

"NAME" VARCHAR2(10)

)

ORGANIZATION external

(

TYPE oracle_loader

DEFAULT DIRECTORY TEST_DIR

ACCESS PARAMETERS

(

    RECORDS DELIMITED BY NEWLINE CHARACTERSET ZHS16GBK

    BADFILE 'TEST_DIR':'test.bad'

    DISCARDFILE 'TEST_DIR':'test.dis'

    LOGFILE 'test.log_xt'                                   

    READSIZE 1048576

    FIELDS TERMINATED BY 0x'09' LDRTRIM

    MISSING FIELD VALUES ARE NULL

    REJECT ROWS WITH ALL NULL FIELDS

    (

      "ID" CHAR(255)

        TERMINATED BY 0x'09',

      "NAME" CHAR(255)

        TERMINATED BY 0x'09'

    )

)

location

(

    'test.txt'

)

)REJECT LIMIT UNLIMITED

這樣就可以利用生成的語句建立外部表，運作上面的語句建立外部表

運作查詢外部表以驗證外部表建立是否成功！

select * from "SYS_SQLLDR_X_EXT_TEST"

結果如下：

ID NAME

1 a

2 b

3 c

4 d

5 e

6 f

至此，外部表建立完畢！！！

<七>使用外部表執行個體:使用Oracle的外部表查詢警告日志檔案

對于DBA來說，最常見一個例子是可以使用外部表來通路警告日志檔案或其他跟蹤檔案.

以下一個例子用來說明外部表的用途。

首先需要建立一個Directory：

[[email protected] oracle]$ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.4.0 - ProdUCtion on Sun Oct 15 21:42:28 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production

With the Partitioning option

JServer Release 9.2.0.4.0 - Production

SQL> create or replace directory bdump

2 as '/opt/oracle/admin/eygle/bdump';

Directory created.

SQL> col DIRECTORY_PATH for a30

SQL> col owner for a10

SQL> select * from dba_directories;

OWNER DIRECTORY_NAME DIRECTORY_PATH

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

SYS BDUMP /opt/oracle/admin/eygle/bdump

然後建立一個外部表:

SQL> create table alert_log ( text varchar2(400) )

2 organization external (

3 type oracle_loader

4 default directory BDUMP

5 Access parameters (

6 records delimited by newline

7 nobadfile

8 nodiscardfile

9 nologfile

10 )

11 location('alert_eygle.log')

12 )

13 reject limit unlimited

14 /

Table created.

然後我們就可以通過外部表進行查詢警告日志的内容:

select * from alert_log where text like 'ORA-%';