oracle 外部文件

2008-12-31
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

http://arvinnan.iteye.com/blog/308638



定义
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.

u 创建的语法类似于： "CREATE TABLE ... ORGANIZATION EXTERNAL"

u 数据在数据库的外部组织，是操作系统文件。

u 操作系统文件在数据库中的标志是通过一个逻辑目录来映射的。       

u 数据是只读的。（外部表相当于一个只读的虚表）

u 不可以在上面运行任何 DML 操作，不可以创建索引。    

u 可以查询操作和连接。可以并行操作。





建立外部表的步骤：
    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