oracle 外部表使用小结

1. 先创建外部表路径

创建一个逻辑Directory：

    create directory TestTable_diras 'D:\Test' ;

授权

   grant read,write on directory TestTable_dir to bruce;

  可以删除一个目录

   DROP DIRECTORY TestTable_dir;

 或者更新时创建TestTable_dir  as 'D:\Test' ;

  create or replace 

2. 创建一个外部表

create table T_XX_EXT
(
    "EVENT_TIME"   Timestamp(3),
    "NOTIFICATION_TYPE"  NUMBER(5,0),
    "NodeId"       NUMBER(15,0),  --大小一定足够大，否则查询报错！！！
    "BtsId"        NUMBER(15,0),
    "AlarmCode"    NUMBER(20,0)
)
 organization external (
  type oracle_loader
  default directory netmax_extern_files_dir
  access parameters (
  records delimited by newline
  badfile 'BADFILE.TXT'
  discardfile 'DISCARDFILE.TXT'
  logfile 'LOGFILE.TXT'
   NODISCARDFILE
  fields terminated by ',' OPTIONALLY ENCLOSED BY '"' LDRTRIM
  REJECT ROWS WITH ALL NULL FIELDS
  (
            "EVENT_TIME" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
                                        DATE_FORMAT TIMESTAMP MASK "YYYY-MM-DD HH24:MI:SS",
            "NOTIFICATION_TYPE" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',                                        
            "NodeId" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
            "BtsId" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"',
            "AlarmCode" CHAR(255) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
    
    ) 
  )
  location(
  'FILE1.log',
  'FILE2.log'
  )
 )REJECT LIMIT UNLIMITED;

3. 更改属性

alter table T_XX_EXT
location(
'FILE3.log'
);