Querying the Alert Log using External Tables

Run this PL/SQL block as SYS. This will create the Directory, grant the required privileges on the Directory and then create the External Table in the SYSTEM schema.

DECLARE
  BDumploc  VARCHAR2(200);
  ORASID       VARCHAR2(16);
  ObjectExists EXCEPTION;
  PRAGMA EXCEPTION_INIT(ObjectExists,-955);
BEGIN
   SELECT value
  INTO BDumploc
  FROM v$parameter
  WHERE name='background_dump_dest';
  -- create the directory for the bdump dir
  EXECUTE IMMEDIATE 'CREATE OR REPLACE DIRECTORY bdump_dir AS '''||
    BDumploc||'''';
  -- grant the necessary privileges
  EXECUTE IMMEDIATE 'GRANT READ ON DIRECTORY bdump_dir TO system';
  -- get the SID
  SELECT instance_name INTO ORASID FROM v$instance;
  -- create the external table
  EXECUTE IMMEDIATE 'CREATE TABLE system.ALERT_LOG_EXT
    (TEXT VARCHAR2(255)
    ) ORGANIZATION EXTERNAL
    (TYPE ORACLE_LOADER
     DEFAULT DIRECTORY BDUMP_DIR
     ACCESS PARAMETERS
     (records delimited by newline
      nobadfile
      nologfile
     )
     LOCATION (''alert_'||ORASID||'.log'')
    )
    REJECT LIMIT UNLIMITED'
  ;
EXCEPTION WHEN ObjectExists THEN NULL;
END;
/

Create the function which will handle dates in the alert log

CREATE OR REPLACE FUNCTION system.get_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;
/

Create the View to query the External Table

CREATE OR REPLACE VIEW system.read_alert_log as
SELECT
      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(system.get_alert_log_date(text),ROWNUM,NULL) low_row_num
            ,system.get_alert_log_date(text) alert_date
            ,text alert_text
      FROM system.alert_log_ext
     )
;

Query the External Table – this will list all alert log entries for the past day which have the string ‘ORA-’

SELECT alert_text
FROM system.read_alert_log
WHERE start_row IN (SELECT start_row
FROM system.read_alert_log
WHERE REGEXP_LIKE(alert_text,'ORA-')
)
AND alert_date > TRUNC(SYSDATE)-1
/