数据库外表

外表(external table)就像普通的表对像一样，可以select等，只是它是只读的，数据库中只保存了表结构的描述，表数据却没有存放在数据库内，而是存放在了文件系统上。当用户想偶尔使用数据库外的结构化数据时，用起外表来就非常方便，甚至比sqlldr都要方便的多。 
外部表是在数据库以外的文件系统上存储的只读表，例如EXCEL、CSV等文件 
第一步：创建目录并授权。目录是数据文件的存放目标，数据文件通常要求是文本文件。这个过程在9i以前是需要配置utl_file_dir参数的。 
（转http://www.blogjava.net/wxqxs/archive/2008/10/13/237180.html?opt=admin） 
    建立外部表的步骤如下： 

    1、创建以“,”分隔的文件“TestTable.csv”至“D:\Test” 

    2、创建一个Directory： 
    create directory TestTable_dir as 'D:\Test' ; 

    3、创建一个外部表： 
    create table TestTable( 

       ID varchar2 ( 10 ), 

       NAME varchar2 ( 20 ), 

       TYPE varchar2 ( 20 ), 

       AGE varchar2 ( 20 ) ) 

    organization external ( 

       type oracle_loader 

       default directory TestTable_dir 

       access parameters (fields terminated by ',' ) 

       location ( 'TestTable.csv' ) 

       ); 


    需要注意两点： 
    ● 使用number类型时须发查询数据(不知道为什么) 
    ● 在建csv表时无需加表头，否则会多出一行数据来 


各类参数说明 

    1、type oracle_loader 

      数据转换驱动器，oracle_loader为默认，也可以改换其他 

    2、defaultdirectory TestTable_dir 
       location ('TestTable.csv') 

      指定外部表所在文件夹以及指定文件 

    3、accessparameters 

      设置转换参数，例如(fields terminated by',') 表示以','为字段间的分隔符 
      ● 参数由访问驱动程序定义 


外部表的错误处理 

    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 

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

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

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

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

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

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

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