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 
)