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_datapump
              default directory temp_dir
              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:要装载的数据文件的位置。

 

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

外部表的实例：

--创建目录
create or replace directory dir_bdump as 'D:/oracle/product/10.2.0/admin/fgisdb/bdump';
--查询目录
select * from dba_directories;
--查询外部表的目录
select * from dba_external_locations;
--查询外部表
select * from dba_external_tables;
--创建外部表
create table alert_fgisdb ( text varchar2(400) )
  organization external (
  type oracle_loader
  default directory bdump
  access parameters (
  records delimited by newline
  nobadfile
  nodiscardfile
  nologfile
  )
  location('alert_fgisdb.log')
  )
  reject limit unlimited

--通过外部表查找数据库的运行信息

 

select to_char(last_time,'dd-mon-yyyy hh24:mi') shutdown,
       to_char(start_time,'dd-mon-yyyy hh24:mi') startup,
       round((start_time-last_time)*24*60,2) mins_down,
       round((last_time-lag(start_time)over(order by r)),2)days_up,
       case when (lead(r) over (order by r) is null) --lead函数用于取出后N行数据
            then round((sysdate-start_time),2)
       end days_still_up
     from (
   select r,
          to_date(last_time,'Dy Mon DD HH24:MI:SS YYYY') last_time,
          to_date(start_time,'Dy Mon DD HH24:MI:SS YYYY') start_time
     from (
   select r,
          text,
          lag(text,1)over(order by r) start_time,--lag函数用于取出前n行数据
          lag(text,2)over(order by r) last_time
     from (
   select rownum r,text 
          from alert_fgisdb
       where text like'____:_:_20_'
            or text like'starting oracle instance%'
     )
     )
      where text like 'starting oracle instance%'
     )

 

 

lag函数语法：lag（字段，n）

lead函数语法与lag一样。 

 

--更改拒绝限制
ALTER TABLE alert_fgisdb LIMIT 100;
--更改默认目录说明
ALTER TABLE alert_fgisdb DIRECTORY DEFAULT DIRECTORY bdump;
--修改访问参数,如分隔符由","变为"|"
ALTER TABLE alert_fgisdb PARAMETERS ACCESS PARAMETERS (FIELDS TERMINATED BY '|');
--修改文件位置:
ALTER TABLE alert_fgisdb LOCATION('TC_REG_MNGREGIONCODE.txt');
 
drop table alert_fgisdb;
--删除目录
drop DIRECTORY bdump;
--查询外部表（找出alert中含有ora的所有记录）
select * from alert_fgisdb where text like 'ORA-%'; 

--------

-------

工作中遇到的一个外部表的实例

create TABLE stg.STG_MACHINE_STATUS_HIS_EXT204
(
  INST_ID  NUMBER(1),
  CPU      NUMBER(7,2),
  MEM      NUMBER(7,2),
  SYS_TIME DATE
)
organization external
(
  type ORACLE_LOADER
  default directory df_dir
  access parameters 
  (
    RECORDS DELIMITED BY newline
    nologfile
    FIELDS TERMINATED BY ','
    (INST_ID,CPU,MEM,
    SYS_TIME DATE "YYYY-MM-DD HH24:MI:SS"
    )
  )
  location (df_dir:'machine_status_his204')
)

machine_status_his204文件格式为

2, 3, 69.00, 20111008150402
2, 3, 69.00, 20111008150502
2, 5, 69.00, 20111008150603
2, 3, 69.00, 20111008150702
2, 3, 69.00, 20111008150802
2, 2, 69.00, 20111008150902
2, 10, 69.00, 20111008151002
2, 3, 69.00, 20111008151102
2, 3, 69.00, 20111008151202
2, 4, 69.00, 20111008151302
2, 3, 69.00, 20111008151402

此例需要注意外部表date类型的使用