Read Alert Log from External Table
Reading time: 2 - 3 minutes
step 1 : Create procedure
=========================
create or replace procedure ssas_externalalertlog(in_instance in varchar2) as
path_bdump varchar2(4000);
name_alert varchar2(100) := 'alert_' || in_instance || '.log';
begin
select
value into path_bdump
from
sys.v_$parameter
where
name = 'background_dump_dest';
--select
--'alert_' || value || '.log' into name_alert
--from
--sys.v_$parameter
--where
--name = 'db_name';
execute immediate 'create or replace directory ssas_BDUMP as ''' ||
path_bdump || '''';
execute immediate
'create table ssas_ALERTLOG ' ||
' (MSG_line varchar2(4000) ) ' ||
' organization external ' ||
' (type oracle_loader ' ||
' default directory ssas_BDUMP ' ||
' access parameters ( ' ||
' records delimited by newline ' ||
' nobadfile ' ||
' nologfile ' ||
' nodiscardfile ' ||
' skip 0 ' ||
' READSIZE 1048576 ' ||
' FIELDS LDRTRIM ' ||
' REJECT ROWS WITH ALL NULL FIELDS ' ||
' (MSG_LINE (1:1000) CHAR(1000)) ' ||
' ) ' ||
' location (''' || name_alert || ''') )' ||
' reject limit unlimited ' ||
' noparallel nomonitoring ';
end;
/
step 2 : execute the procedure
==============================
execute ssas_externalalertlog('ORACLE_SID')
step 3 : issue this sql statement
=================================
col lineno noprint
col ora_error noprint
col msg_line format a132
set pages 0 lines 300 trimspool on trim on
alter session set nls_date_format='dd/mm/yyyy hh24:mi:ss';
alter session set sql_trace=false;
break on thedate
prompt
prompt ERROR IN ALERT LOG FILE - LAST 3 DAYS
prompt =====================================
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-%')
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 msg_line like '___ ___ __ __:__:__ ____'
then to_date( msg_line, 'Dy Mon DD hh24:mi:ss yyyy' )
else null
end thedate
from ssas_alertlog
)
)
)
)
where ora_error is not null
and thedate >= (trunc(sysdate) - 3)
order by thedate
/
step 4 : drop the external table
================================
drop table ssas_alertlog
/
