Read the alert log with SQL

(1) alert 파일을 재료로 external table 생성하기

[oracle@ora10gr2 ~]$ sqlplus system/oracle
 
SQL> show parameter instance_name
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_name                        string      orcl

SQL> select value from v$parameter where name='background_dump_dest';
 
VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/admin/orcl/bdump
 
SQL> -- 디렉토리 객체 생성
SQL> create or replace directory b_dump_dir as '/u01/app/oracle/admin/orcl/bdump';
 
SQL> -- external table 생성
SQL> create table t_alert_log (text_line varchar2(500)) organization external
         (type oracle_loader default directory b_dump_dir location ('alert_orcl.log'));
 
SQL> -- alert 파일 내용을 질의로 확인
SQL> select * from t_alert_log where rownum <= 20;

(2) 사용자 정의 함수 및 쿼리를 사용해서 질의에 편리한 view 생성

SQL>
 
create or replace function alert_log_date( text in varchar2 )
  return date
is
  invaliddate  exception;
  pragma exception_init(invaliddate, -1846);
begin
  return to_date(text,'dy mon dd hh24:mi:ss yyyy','nls_date_language=american');
exception
  when invaliddate then return null;
end;
/
 
SQL>

create or replace view alert_log
as
select row_num,
         last_value(low_row_num ignore nulls)
         over(order by row_num rows between unbounded preceding
         and current row) start_row,
         last_value(alert_date  ignore nulls)
         over(order by row_num rows between unbounded preceding
         and current row) alert_date,
         alert_text
from (select rownum row_num,
                 nvl2(alert_log_date(text_line),rownum,null) low_row_num,
                 alert_log_date(text_line) alert_date,
                 text_line alert_text
      from t_alert_log);
 
 
SQL> set pages 40
SQL> set lines 200
SQL> col alert_text format a100
SQL> select * from alert_log;

(3) 활용예제

SQL> -- 최근 한시간 동안 추가된 내용 확인
SQL> select * from alert_log
         where alert_date > sysdate - 1/24;
 
SQL> -- 지난 한달동안 추가된 내용 가운데 "ORA-"라는 글자를 포함하는 라인 확인
SQL> select * from alert_log
          where start_row in (select start_row from alert_log
                                          where regexp_like(alert_text,'ORA-'))
          and alert_date > trunc(sysdate,'mon');

출처 : http://blog.naver.com/orapybubu/40050729230