Oracle外部表 External Table

参考

http://www.blogjava.net/wxqxs/archive/2008/10/13/237180.html?opt=admin
http://hi.baidu.com/ray315/blog/item/153c97a33cd26caacbefd0de.html
 
定义

External tables access data in external sources as if it were in a table in the database.
You can connect to the database and create metadata for the external table using DDL.
The DDL for an external table consists of two parts: one part that describes the Oracle
column types, and another part (the access parameters) that describes the mapping of
the external data to the Oracle data columns.

 

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

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

建立外部表的步骤：
    1、创建以“,”分隔的文件“TestTable.csv”至“D:/Test”
    2、创建一个Directory：
    create directory TestTable_diras 'D:/Test' ;
    3、创建一个外部表：
   create table TestTable(
      ID varchar2 ( 10 ),
      NAME varchar2 ( 20 ),
      TYPE varchar2 ( 20 ),
      AGEvarchar2 ( 20 ))
   organization external (
      type oracle_loader
      default directory TestTable_dir
      access parameters (fields terminatedby ',' )
      location ( 'TestTable.csv' )
      );

各类参数说明

    1、type oracle_loader
      数据转换驱动器，oracle_loader为默认，也可以改换其他
    2、defaultdirectory TestTable_dir
       location ('TestTable.csv')
      指定外部表所在文件夹以及指定文件
    3、accessparameters
     设置转换参数，例如(fields terminatedby',')表示以','为字段间的分隔符
      ● 参数由访问驱动程序定义

外部表的错误处理
    1、REJECT LIMIT子句
      在创建外部表时最后加入LIMIT子句，表示可以允许错误的发生个数。
      * 默认的REJECT LIMIT值为0
      * REJECT LIMIT UNLIMITED则不会报错
    2、BADFILE 和 NOBADFILE 子句
      在accessparameters中加入BADFILE'BAD_FILE.txt'子句，则所有数据转换错误的值会被放入'BAD_FILE.txt'中
      使用NOBADFILE子句则表示忽略转换错误的数据
      ● 如果不写BADFILE或NOBADFILE，则系统自动在源目录下生成与外部表同名的.BAD文件
      ● BADFILE只能记录前1次操作的结果，他会被第2次操作所覆盖。
    3、LOGFILE 和 NOLOGFILE 子句
      在accessparameters中加入LOGFILE'LOG_FILE.log'子句，则所有Oracle的错误信息放入'LOG_FILE.log'中
      使用NOLOGFILE子句则表示不记录错误信息到log中
      ● 如果不写LOGFILE或NOLOGFILE，则系统自动在源目录下生成与外部表同名的.LOG文件
 
修改外部表语句
    外部表与堆表一样可以之用ALTER TABLE命令修改表属性
    * REJECT LIMIT        --错误数
    * DEFAULT DIRECTORY   --默认目录
    * ACCESS PARAMETERS   --参数
    * LOCATION            --数据文件
    * ADD COLUMN          --增加列
    * MODIFY COLUMN       --列定义
    * DROP COLUMN         --删除列
    * RENAME TO           --外部表更名

其他约束
    ● 外部表无法使用insert、update、delete等操作，要修改其数据只能通过修改数据文件。
    ● 外部表不能建立索引，如要建立，则需要先create table XX as select * from TestTable

PS:
     1.外部表可以加载和卸载数据泵格式的数据，只需把organization external里的参数type设置为oracle_datapump。
            create table all_objects_unload
            organization external
                   (
                   type oracle_datapump
                  default directory testdir
                  location('allobjects.dat')
                  )
            as
            select * from all_objects

 

 

使用外部表时有以下几个限制。

 

    限制一：需要先建立目录对象。

    在使用外部数据表的时候，需要先建立目录对象。也就是说，要想Oracle数据库系统访问操作系统文件，就必须在数据库中建立指向这个操作系统文件的目录对象，然后通过这个目录对象访问相应的操作系统文件，即用目录对象存储操作文件的保存位置。不过需要注意，这个目录对象普通用户是无权创建的。一般情况下只有特权用户或者数据库管理员角色才可以创建目录对象。如果允许其他用户也可以创建目录对象的话，就需要授权给其他用户。

    其实这个目录对象就是操作系统中文件或者表格的存储位置。不过在建立对象的时候，需要小心，Oracle数据库系统不会去确认这个目录是否真的存在。如果在输入这个目录对象的时候，不小心把路径写错了，那可能这个外部表仍然可以正常建立，但是却无法查询到数据。由于建立目录对象时，缺乏这种自我检查的机制，为此在将路径赋予给这个目录对象时，需要特别的注意。另外需要注意的是路径的大小写。在Windows 操作系统中，其路径是不区分大小写的。而在Linux操作系统，这个路径需要区分大小写。故在不同的操作系统中，建立目录对象时需要注意这个大小写的差异。最好数据库管理员可以养成一个习惯，无论在什么操作系统中，都采用小写的目录名与文件名。那么在不同的操作系统中，就不用受到这个大小写的困扰了。最后需要注意的是，目录对象名必须唯一。如果目录对象名字有重复的话，系统会提示错误信息：“名称已有现有对象引用”，而不会自动覆盖原有的对象名设置。

    虽然这个对象名设置比较简单，但是其是数据库寻找操作系统上文件的唯一途径。所以这个目录对象的创建必须确保准确。否则的话，外部表就可能无法正常使用。

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

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

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

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

    限制三：在建立临时表时的限制。

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

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

　　  限制四：删除外部表或者目录对象。

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

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

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

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