Untitled Document

Oracle Alert Log as External Table

The following will create an external table called ALERT_LOG for the actual alert log file. This will allow you to do things, such as viewing the alert log for teh past X amount of hours from the SQL prompt. For example

SELECT SHOW_LOG(SYSDATE-1) FROM DUAL;

will allow you to view alert log inthe past 24 hours.

First we create a directory:

declare
   path_bdump varchar2(4000);
   name_alert varchar2(100);
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 bdump as ''' ||path_bdump || '''';
end;
/

Then lets define alert table as an external table:

declare
   path_bdump varchar2(4000);
   name_alert varchar2(100);
   V_ISTABLE number;
begin
   select count(1) into V_ISTABLE from user_tables where table_name='ALERT_LOG';
   if V_ISTABLE = 1 then
     execute immediate ('drop table alert_log');
   end if;
   select 'alert_' || value || '.log' into name_alert from sys.v_$parameter where name = 'db_name';

   execute immediate
   'create table alert_log ' ||
   ' (text varchar2(400) ) ' ||
   ' organization external ' ||
   ' (type oracle_loader ' ||
   ' default directory BDUMP ' ||
   ' access parameters ( ' ||
   ' records delimited by newline ' ||
   ' nobadfile ' ||
   ' nologfile ' ||
   ' nodiscardfile ' ||
   ' ) ' ||
   ' location (''' || name_alert || ''') )' ||
   ' reject limit unlimited ';
end;
/

Then we need to create some additional functions that parse the alert_log table

CREATE OR REPLACE FUNCTION IS_DATE_FIELD(I_TEXT IN VARCHAR2)
RETURN DATE
IS
   V_DATE DATE;
BEGIN
   V_DATE:=TO_DATE(REPLACE(RTRIM(LTRIM(I_TEXT)),' ',' '),'Dy Mon DD HH24:MI:SS YYYY');
   RETURN V_DATE;
EXCEPTION
WHEN OTHERS THEN
   RETURN NULL;
END;
/

CREATE OR REPLACE FUNCTION GET_LAST_ALERT_LINE(I_LD IN DATE)
RETURN NUMBER
IS
   V_LN NUMBER;
BEGIN
   FOR I IN (SELECT TEXT,ROWNUM FROM ALERT_LOG ORDER BY ROWNUM DESC) LOOP
      IF (IS_DATE_FIELD(I.TEXT) <= I_ld) THEN
         V_LN:=I.ROWNUM;
         RETURN V_LN;
      END IF;
   END LOOP;
   RETURN 0;
END;
/

And Finally this function will return everything to you from the alert log that is past the input date. You may also modify this function to return only the line that you want, for exampel lines that contain ERRORS:

CREATE FUNCTION SHOW_LOG(I_LD IN DATE)
RETURN VARCHAR2
AS
   V_LN NUMBER;
   V_STRING VARCHAR2(32000):=NULL;
   CRLF VARCHAR2( 2 ):= CHR( 13 ) || CHR( 10 );
BEGIN
   V_LN:=GET_LAST_ALERT_LINE(I_LD);
   FOR I IN (SELECT * FROM (SELECT TEXT,ROWNUM AS LINENO FROM ALERT_LOG ORDER BY ROWNUM DESC) WHERE LINENO >= V_LN)
   LOOP
     V_STRING:=V_STRING||I.TEXT||CRLF;
   END LOOP;
   RETURN SUBSTR(V_STRING,0,32000);
END;
/