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/test@acf 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：
[oracle@jumper 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-%';