Mining the alert.log

  SELECT value
  FROM v$parameter
  WHERE name='background_dump_dest';
  
  CREATE OR REPLACE DIRECTORY bdump_dir AS '/ora/mta/admin/dmtaa1/bdump'
  
  select instance_name from v$instance
  
  --GRANT READ ON DIRECTORY bdump_dir TO system
  
  CREATE TABLE ALERT_LOG_EXTERNAL
    (TEXT VARCHAR2(255)
    ) ORGANIZATION EXTERNAL
    (TYPE ORACLE_LOADER
     DEFAULT DIRECTORY BDUMP_DIR
     ACCESS PARAMETERS
     (records delimited by newline
      nobadfile
      nologfile
     )
     LOCATION ('alert_dmtaa1.log')
    )
    REJECT LIMIT UNLIMITED
  
SELECT * FROM alert_log_external WHERE ROWNUM < 20

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;

create or replace view alert_log_base as 
SELECT ROWNUM row_num,alert_log_date(text) alert_date, text, 
  NVL2(alert_log_date(text),ROWNUM,NULL) low_row_num,
  decode(substr(text, 1, 4), 'ORA-', 1, decode(substr(text, 1, 6), 'Errors', 1, 0)) ORA_ERR 
  FROM alert_log_external
  
SELECT * FROM alert_log_base

CREATE OR REPLACE VIEW alert_log as
SELECT b.*
      ,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_prop
FROM alert_log_base b

SELECT * FROM alert_log
order by alert_date_prop desc