Oracle之外部表

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 directorytest_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))

ORGANIZATIONEXTERNAL

(

TYPEORACLE_LOADER

DEFAULTDIRECTORY test_dir

ACCESS Parameters

(

RECORDSDELIMITED 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> DESCDBA_EXTERNAL_TABLES;

Name TypeNullable

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

OWNERVARCHAR2(30)

TABLE_NAMEVARCHAR2(30)

TYPE_OWNERCHAR(3) Y

TYPE_NAME VARCHAR2(30)

DEFAULT_DIRECTORY_OWNERCHAR(3) Y

DEFAULT_DIRECTORY_NAME VARCHAR2(30)

REJECT_LIMITVARCHAR2(40) Y

ACCESS_TYPEVARCHAR2(7) Y

ACCESS_PARAMETERS VARCHAR2(4000) Y

SQL>SELECTOWNER,TABLE_NAME,DEFAULT_DIRECTORY_NAME,ACCESS_PARAMETERSFRFROM

DBA_EXTERNAL_TABLES;

可以得到外部表的相关信息;

 

<三>:如何得到外部路径的信息:

SQL> desc DBA_EXTERNAL_LOCATIONS;

得到该表结构:

Name Type Nullable

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

OWNERVARCHAR2(30)

TABLE_NAMEVARCHAR2(30)

LOCATIONVARCHAR2(4000) Y

DIRECTORY_OWNERCHAR(3) Y

DIRECTORY_NAME VARCHAR2(30) Y

SQL> select * fromDBA_EXTERNAL_LOCATIONS;

得到具体信息

 外部表的含义：

    外部表是指不在数据库中的表，如操作系统上的一个按一定格式分割的文本文件或者其他类型的表。这个外部表对于Oracle数据库来说，就好像是一张视图， 在数据库中可以像视图一样进行查询等操作。这个视图允许用户在外部数据上运行任何的SQL语句，而不需要先将外部表中的数据装载进数据库中。不过需要注意是，外部数据表都是只读的，不能够更改。

外部表使用限制：（来源于http://www.examda.com/oracle/zonghe/20101031/141746967.html）

    需要先建立目录对象：在创建外部表之前先要创建一个这个外部表要指向的文件所在目录路径的目录；

    对于操作系统文件的限制：

    其实外部表简单的说，就是跟操作系统上固定格式的文件或者表格的一个连接。为了Oracle数据库系统能够正确链接外部表，对于外部表的格式就提出 了比较严格的要求。如果不符合这些要求的话，数据库系统就无法正确读取外部表中的数据。如对于分隔符有比较严格的要求。虽然在外部文件或者表格中，可以使 用多种分隔符，如英文状态下的逗号或者分号等等。但是有一个限制，即在同一个操作系统文件中只能够使用一个分割符号，要么逗号或者分号等等。因为在建立外 部表时，必须指定操作系统文件所使用的分隔符号。如果有多种分隔符号的话，数据库系统将无法识别。

　　另外在外部表格中，不能够带有标题信息。如现在有一张表格，以逗号分隔。而在其第一列数据中有各个列的标题信息。而数据库系统在连接这个表的时 候，会将这些标题信息当作普通的纪录来对待。即会将这些信息也显示在外部表中。为此如果这个标题信息与外部表的字段类型不一致（如字段内容是number 数据类型，而标题信息则是字符型数据，则在查询时就会出错）。如果数据类型恰巧一致的话，这个标题信息Oracle数据库也会当作普通记录来对待。如在建 立外部表的时候，最好确认一下操作系统文件中是否包含标题信息。如果有的话，需要删除。否则的话，可能会出错。

　　最后需要说明的是，当Oracle数据库系统访问这个操作系统文件的时候，会在这个文件所在的目录自动创建一个日志文件。无论最后是否访问成功，这个日志文件都会如期建立。查看这个日志文件，可以了解数据库访问外部表的频率、是否成功访问等等。  

在建立临时表时的限制：

    在建立临时表时，也会有不少的限制。如表中字段的名称有一些特殊字符的话，那么这个表列的名称必须使用英文状态的下的双引号连接起来。如采用 “studentno#”。遇到列名字中有特殊符号时，如果不采用双引号括起来，虽然临时表可以正常创建，但是在采用的时候会出现错误，无法正常查询数 据。如数据库系统可能会提醒：“数据库插件错误”等信息。为此最好在创建临时表时不要在列名中使用一些特殊的字符。其实不光光是建立临时表有这种限制，建 立其他标或者试图都有类似的限制。

　　其次，这个外部表毕竟与内部表不同。在创建外部表的时候，其实在数据库中跟本没有创建表。也就是说，不会为外部表分配任何的存储空间。创建外部 表只是在数据字典中创建了外部表的元数据，以便对应访问外部表中的数据，而不在数据库中存储外部表的数据。简单地说，数据库存储的只是与外部文件的一种对 应关系，如字段与字段的对应关系。而没有存储实际的数据。为此在表的操作与管理上，就会受到很大的限制。如在外部表上，是不能够为表创建索引。因为创建索 引就意味着要存在对应的索引记录。而外部表其实在数据库不会有存储。故在外部中是无法建立索引的。如果硬要建立的话，则系统会提示“操作在外部组织表上不 受支持”的错误提示。同样的道理，在数据库中也不能够更新外部表中的数据，如插入记录、删除记录或者更新信息等等。简而言之，这个外部表对于数据库来说， 是只读的，不可更新。

    删除外部表或者目录对象：

    当外部表不用时，需要及时删除外部表或者与之对应的目录对象。不过在删除这些内容时会有一些限制。这些限制主要是管理上的限制，而不是技术上的限 制。也就是说，Oracle数据库系统没有对其进行强制的限制。但是如果数据库管理员不遵守这些限制的话，可能会出现一些问题。如要先删除外部表，然后再 删除目录对象。有时候一个目录对象中可能会包含多个外部表。此时必须要确认所有的外部表都不用了，都已经删除干净了，然后才能够删除目录对象。在创建外部 表时，操作系统会判断一下，与之对应的目录对象是否已经创建。但是在删除对象时，系统不会去判断跟这个目录对象关联的外部表是否已经全部删除。如果目录对 象删除了，但是还有外部表存在。此时查询这个外部表的时候，系统就会提示“对象不存在”的错误信息。所以这个删除目录对象时，数据库系统缺乏一种检查，此 时只有数据库管理员在删除目录对象时，先手工确认一下这个目录对象是否存在其他的外部表。

　　要了解这个信息，则可以通过查询dba_external_locations。通过查询这张表，系统会反映当前所有的目录对象以及相关的外部 表，还会查询出这些外部表所对应的操作系统文件的名字。先查询这张表格，确定要删除的对象没有其他关联的外部表时，再进行删除。否则的话，需要先确认其他 外部表的可用性。免得因为误删除而导致外部表无法正常使用。

    对于操作系统平台的限制：

    虽然Oracle数据库是支持跨平台的数据库系统，即同时支持Windows或者Linux等多种操作系统。但是在使用外部表的时候需要注意一个问题，即 在两个操作系统上文本文件的存储方式是不同的。如在Windows操作系统上利用txt文件建立了一个以逗号作为分隔符的文件，其一行一条记录。但是在 Linux操作系统上打开的话，在其就可能使在同一行中显示了。故为了数据库系统能够正确识别操作系统文件，最好这个操作系统文件能够和Oracle数据 库系统部署在同一台服务器上或者同一种操作系统上。否则的话，很可能因为格式的冲突，而导致数据库系统无法正确读取外部文件中的数据。