Accessing Alert Log via SQL with External Tables Don Burleson

Starting in Oracle9i you can map external flat files to Oracle tables and in 11g you can directly query the alert log with SQL using the x$dbgalertext table.

Note:  It can take weeks to write a comprehensive set of intelligent scripts to scan the alert logs for important massages.  If you don't want to write your own custom scripts, see my Oracle script download.

This is especially useful for reading the Oracle alert log and mapping a Excel spreadsheet making the Oracle alert log accessible via SQL from Oracle.  See using external tables in spreadsheets.

Mapping the Oracle alert log is easy and once defined, all you have to do is query it with standard SQL syntax:

create directory BDUMP as '/u01/app/oracle/admin/mysid/bdump';

create table
   alert_log ( msg varchar2(80) )
organization external (
   type oracle_loader
   default directory BDUMP
   access parameters (
      records delimited by newline
   )
   location('alrt_mysid.log')
)
reject limit 1000;

Now we can easily extract important Oracle alert log information without leaving SQL*Plus, something like this SQL:

select msg from alert_log where msg like 'ORA-00600%';

ORA-00600: internal error code, arguments: [17034], [2940981512], [0],[],[],[],[],[]
ORA-00600: internal error code, arguments: [18095], [0xC0000000210D8BF8],[],[],[],[],[]
ORA-00600: internal error code, arguments: [4400], [48],[],[],[]