Oracle之外部表[转]

有时候，数据库海量数据的存储，是一个令人头疼的问题。别的不说，光是频繁的执行insert（1000次/秒）都是一场恶梦。但是，如果将数据保存到文家里，而不是数据库中，数据序列化的开销就小得多了。但是，对于文件的各种复杂检索，又是一件相当麻烦的事。

    幸好，Oracle有这样一种特性，它可以将某些特定格式的文件映射到数据库中，形成一个“表”，称为“外部表”。单用户更改文件内容时，外部表中的数据即随之改变。同时，用户又可以像检索普通表一样，以只读的方式对外部表进行检索。

    我们假设有这样一个文件（DATA.TXT）：
1|this is a string
2|这里是个字符串
3|ABC
    要把这样一个文件映射成外部表，有以下工作要做：
    首先，我们需要为Oracle创建一个Directory，

    创建方式为，在数据库中执行，须用DBA用户创建，并给应用授权。
    create directory EXT_TABLE_DIR as '/home/oracle/app/oracle/oradata/php/'
    注意“/home/oracle/app/oracle/oradata/php/”是一个存在于Oracle数据库服务器本身上边的实际存在的文件夹；(服务器路径)
    然后，将DATA.TXT文件拷贝到上述文件夹下；
    最后，创建一个对应外部表，
create table EXT_TABLE_NAME
(
COL_1 NUMBER,
COL_2 VARCHAR2(512)
)
organization external
(
type oracle_loader
default directory EXT_TABLE_DIR
access parameters ( fields terminated by '|' )
location ('DATA.TXT')
)
reject limit unlimited
    注意蓝色部分，EXT_TABLE_NAME是要映射成的外部表名称，EXT_TABLE_DIR是第一步里我们创建的Oracle的Directory，“|”是文件里的分割符，DATA.TXT是文件名。

    需要补充的是，最后有一句“reject limit unlimited”，告诉Oracle这个外部表没有行数限制。否则，当文件中的数据量超过200万行时，在对表进行检索时，就会出现ORA-30653，“reject limit reached”错误。

 

Oracle 9i 的一项新特性就是 External Table，它就象通常的数据库表一样，拥有字段和数据类型约束，并且可以查询，但是表中的数据却不存储在数据库中，而是在与数据库相关联的普通外部文件里。当你查询 External Table 时，Oracle 将解析该文件并返回符合条件的数据，就象该数据存储在数据库表中一样。
具体的定义可以参见《 Oracle 概念手册》，以下的几点需要注意：

<一>:外部表的描述:

> 创建的语法类似于： "CREATE TABLE ... ORGANIZATION EXTERNAL"
> 数据在数据库的外部组织，是操作系统文件。
> 操作系统文件在数据库中的标志是通过一个逻辑目录来映射的。
> 数据是只读的。（外部表相当于一个只读的虚表）
> 不可以在上面运行任何 DML 操作，不可以创建索引。
> 可以查询操作和连接。可以并行操作。

例子:
假如有如下两个数据文件:
1: 数据文件的格式
F1.TXT文件:

13234,FIRSTS
46464,TESTA

F2.TEXT文件:

13234,SECONDS
46464,TEST
2:创建目录,并用DBA进行授权;
sql> create directory test_dir as 'E:temp';
sql>grant read,write on directory test_dir to users;
注意：创建完毕逻辑目录之后要把平面文件拷贝到该目录下，另外还要注意文件名字不要写错。

      一定要给oracle用户对这个目录可读可写的权限，操作系统层面，如使用chmod -R 777 test_dir；
3:使用被授权的用户users创建外部表:
create table test_table
(ms_no varchar(20),
tip varchar(20),
descs varchar(20))
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY test_dir
ACCESS Parameters
(
RECORDS DELIMITED BY NEWLINE
badfile 'bad_dev.txt'
LOGFILE 'log_dev.txt'
FIELDS TERMINATED BY ','
MISSING FIELD VALUES ARE NULL
(ms_no,tip,descs)
)
LOCATION('F1.txt','F2.txt')
)
;
表创建完成.当然也可以导入一个文件

4:进行SELECT 操作看是否正确;
SQL>select * from test_table
结果如下:
MS_NO TIP DESCS
-------------------- -------------------- --------------------
13234 FIRSTS
46464 TESTA
13234 SECONDS
46464 TEST

 

<二>: 如何得到外部表的有关信息:
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;
得到具体信息;