PL/SQL to review the alert.log using it as an external table

First we need to create a logical directory and declare the alert as an external table. I use the oracle_loader drive in order to avoid the modification of the alert.log, but in 10G you can declare it using the ORACLE_DATAPUMP drive if you wish. I show the error line number, if any, in order to locate it easily with the editor.

create directory dir_alert as 'C:\ORANT\ORA102\...\DWHPRO\BDUMP\';

create table alert(
texto varchar2 (120 char) null)
organization external(
type oracle_loader
default directory dir_alert
access parameters (records delimited by newline)
location (dir_alert:'alert_dwhpro.log')
) reject limit 1000;

Here the PL/SQL I use to search for "ORA-" errors in the last lines (comments are in spanish):


set serveroutput on
set linesize 140
set pagesize 0

/******************************************************
* CONTAJE DE LOS ERRORES "ORA-" EN LAS ÚLTIMAS LÍNEAS *
******************************************************/
DECLARE
revisadas constant integer := 1000;
ultima number;
errores number;
fecha_inicio varchar2(120):='';
buscar boolean:=true;
cursor c1 (ultima in number) is
select * from (select row_number() over (order by ROWNUM) linea, texto
from alert)
where linea > ultima-revisadas;

fc1 c1%rowtype;
anterior c1%rowtype;

BEGIN
errores:=0; ultima:=1;
select max(linea) into ultima
from (select row_number() over (order by ROWNUM) linea, texto from alert);
open c1(ultima);
fetch c1 into fc1;anterior:=null;
loop
if fc1.texto like 'ORA-%' then
errores:= errores+1;
dbms_output.put_line('ERROR Numero '||to_char(errores,'99')||':');
dbms_output.put_line('LINEA MENSAJE');
dbms_output.put_line('-------------------------------------------');
dbms_output.put_line(to_char(anterior.linea,'99G999')
||' '||anterior.texto);
dbms_output.put_line(to_char(fc1.linea,'99G999')||' '||fc1.texto);
dbms_output.put_line('-------------------------------------------');
dbms_output.put_line(CHR(10));
end if;
anterior:=fc1;
if buscar and fc1.texto like '%'||extract(year from sysdate) then
buscar:=false;
fecha_inicio:=fc1.texto;
end if;
fetch c1 into fc1;
exit when c1%notfound;
end loop;
close c1;
dbms_output.put_line(CHR(10));
dbms_output.put_line('RESULTADO');
dbms_output.put_line('---------'||CHR(10));
dbms_output.put_line('Entre ['||fecha_inicio||' ---> ahora]
(ultimas '||revisadas||' lineas del alert)');
if errores=0 then dbms_output.put_line('No hay errores.');
else
dbms_output.put_line('Hay '||errores||' error(es).');
end if;
END;
/


Example of output:

SQL> @d:\dba\errores
ERROR Numero 1:
LINEA MENSAJE
---------------------------------------------------------------------------------
43.850 Tue Feb 05 04:57:27 2008
43.851 ORA-DUMMY This is an error
---------------------------------------------------------------------------------




RESULTADO
---------

Entre [Sat Feb 02 15:50:17 2008 ---> ahora] (ultimas 1000 lineas del alert)
Hay 1 error(es).

Procedimiento PL/SQL terminado correctamente.

In a similar way yo can list the last lines in the alert.log, I do it every morning.