Oracle的外部表

外部表作用:加载和卸载数据

 

外部表都是只读表 , 不能进行 update,insert,delete 操作 .

Oracle 提供两种访问驱动 :

ORACLE_LOADER ：   ORACLE_DATAPUMP(oracle 10.2 后新增的驱动 )

ORACLE_LOADER ： 它利用 oracle loader 技术从外部表读取数据 . 它还具有类似 SQL*Loader 工具控制文件语法的数据映射能力 .

ORACLE_DATAPUMP ：它提供卸载数据的能力 , 即把数据从数据库里导入一个外部表 , 再由一个或几个外部表导入到数据库里 . 对于 ASsubquery 的子句必须使用 ORACLE_DATAPUMP . 创建用于卸载数据的外部表时，不能制定列名。同时，不支持 badfile 、 discardfile 参数，因为其无效或者丢弃数据将不予以记录为操作系统文件 , 没有 fields erminated by( 或者 missing field values are null) 参数

 

ORACLE_DATAPUMP 一般使用在进行日志表等大数据表的备份和数据转移上 . 唯一不足的是 Oracle 只能将数据写成二进制格式 , 所以用外部表来生成 Excel 报表之类的是行不通的 . 不过在 web 应用中用外部表加载 Excel 数据却是可以的而且很大程度上提高应用的性能 , 后面会进行相关举例 .

ORACLE_DATAPUMP 使用举例(备份表数据到平面文件):
      CREATE TABLE external_dx_sms_bak0127 (
           school_id,
           user_id,
           object_mobile,
           stu_sequence
       )
       ORGANIZATION EXTERNAL
       (
         TYPE ORACLE_DATAPUMP
         DEFAULT DIRECTORY temp_dir
         LOCATION('external_dx_sms_bak0127.DMP')
       )
       AS SELECT school_id,user_id,object_mobile,stu_sequence  FROM gz_dx_sms;

      查看平面文件可以用strings
      $strings external_dx_sms_bak0127.DMP

如果此时另一个库需要此份数据,则可以再通过外部表进行加载
处理步骤:

      1.在数据库服务器上建个文件夹存放平面文件,假如路径如下:
         /data/chenqian
      2.创建一个外部表加载路径
         create or replace directory temp_dir as '/data/chenqian';
      3.进行加载数据生成外部表
          create table external_dx_sms_bak0127(
              school_id number(11),
              user_id number(11),
              object_mobile number(30),
              stu_sequence varchar2(30)
          )
          organization external (
              type oracle_loader
              default directory temp_dir
              access parameters (fields terminated by ',' )
              location ('external_dx_sms_bak0127.DMP')
          );
       4.从外部表提取数据到正式表
          方式很多:insert /*+ append*/ 直接插入; insert into ;create table as 等等.
          insert into dx_sms_log as select *  from external_dx_sms_bak0127;
          注意:可以结合merge into 处理存在就更新不存在就插入的情况.

可以使用外部表加载Excel表格到数据中,而且性能效率很高,比web应用中使用poi上传处理上传的Excel数据要快很多.
特别是大批量数据上传时.(另附:这里就觉得使用外部表还有个优势是,可以直接在客户端执行,不像sqlloader那样需要登录到
服务器上才能进行处理)

加载Excel数据步骤 :
        1.将Excel转换成csv的逗号相隔的文件格式
        2.同上面对加载语句只要将external_dx_sms_bak0127.DMP改成'external_dx_sms_bak0127.csv
做测试时加载成功后进行查询时出现ORA-29913 ORA-06512错误,经查询发现是由于在创建时定义的列的宽度太小或者文件路径

不正确导致.





其它的一些外部表示例：

A  加载时只取平面文件里面的几个字段
    SQL> CREATE TABLE emp_load
    2    (employee_number      CHAR(5),
    3     employee_dob         CHAR(20),
    4     employee_last_name   CHAR(20),
    5     employee_first_name  CHAR(15),
    6     employee_middle_name CHAR(15),
    7     employee_hire_date   DATE)
    8  ORGANIZATION EXTERNAL
    9    (TYPE ORACLE_LOADER
    10     DEFAULT DIRECTORY def_dir1
    11     ACCESS PARAMETERS
    12       (RECORDS DELIMITED BY NEWLINE
    13        FIELDS (employee_number      CHAR(2),
    14                employee_dob         CHAR(20),
    15                employee_last_name   CHAR(18),
    16                employee_first_name  CHAR(11),
    17                employee_middle_name CHAR(11),
    18                employee_hire_date   CHAR(10) date_format DATE mask "mm/dd/yyyy"
    19               )
    20       )
    21     LOCATION ('info.dat')
    22    );

    Table created.


B 加载时相关参数设置，包括异常外部表和异常数据生成等。
   reate table ext1(
    id1 int,
    id2 int
    )
    organization external(
    type oracle_loader
    default directory ext_table
    access parameters(
         records delimited by newline
         badfile 'ext_bad.txt'
         logfile 'ext_log.txt'
         fields terminated by "," ldrtrim
         missing field values are null)
    location (ext_table:'extable.txt')
    );

  type:指示使用什么驱动器。
  default directory:指示如果后面的badfile等不指定目录对象时，默认的目录。
  records delimited by newline:记录之间的分隔方式,此处是换行符分隔,有多个选项
                               也可以用如records delimited by '|'，表示'|'结尾
  records fixed 10:每10个字符为一行记录
  records variable 2:实际记录中，每条记录的前2位数字为本条记录的长度提示符。
                               如“10abcd50efg715……”10之后的十个字符（到15前）为一条记录，
                              15之后的15个字符为第二条记录。
  badfile,logfile:可以指定目录，如badfile bad_dir:'ext_bad.txt'
                        同样可以对这些文件建立外部表
  location:要装载的数据文件的位置。