external table + alert_sid.log

创建 directory

– create alert directory

select *
from dba_directories dd
where 1=1
and dd.directory_name='ALERT_DIR'
;

create or replace directory alert_dir as '/oracle/admin/orcl/bdump'

alert_dir 需要根据 oracle 版本不同而不同
– grant

grant read,write on directory ALERT_DIR to system
; 

创建外部表

– Create table

create table ALERT_LOG_VIEW_ORCL
(
  MSG_LINE VARCHAR2(4000)
)
organization external
(
  type ORACLE_LOADER
  default directory ALERT_DIR
  access parameters 
  (
    records delimited by newline
    nobadfile
    nologfile
    nodiscardfile skip 0
    READSIZE 10485760 FIELDS LDRTRIM
    REJECT ROWS WITH ALL NULL FIELDS
    (MSG_LINE (1:1000) CHAR(1000))
  )
  location (ALERT_DIR:'alert_orcl.log')
)
reject limit UNLIMITED;   

查询外部表

select                 
       LINENO, 
       THEDATE, 
       ORA_ERROR, 
       MSG_LINE
from (        
  select LINENO, 
         THEDATE, 
         ORA_ERROR, 
         MSG_LINE
  from (select *
          from (
           select lineno,
                  msg_line,
                  thedate,
                  max(case
                       when (   ora_error like '%ORA-%' 
                             or ora_error like '%PLS-%'
                             or ora_error like '%TNS-%' 
                             or ora_error like '%WARNING%'
                             ) then
                        rtrim(substr(ora_error, 1, instr(ora_error, ' ') - 1),
                              ':')
                       else
                        null
                     end) over(partition by thedate) ora_error
            from ( 
               select lineno,
                      msg_line,
                      max(thedate) over(order by lineno) thedate,
                      lead(msg_line) over(order by lineno) ora_error
                from ( select rownum lineno,
                              substr(msg_line, 1, 132) msg_line,
                              case
                                when replace(msg_line,'CST ','') like '___ ___ __ __:__:__ ____' 
                                          then to_date(replace(msg_line,'CST ',''),'Dy Mon DD hh24:mi:ss yyyy')
                                else null
                              end thedate
                        from alert_log_view_orcl
                      )
                  )
             )
       )
 where 1=1
   and ora_error is not null
   and thedate >= (sysdate - 1)
 order by thedate
)