Access your 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.

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:

http://www.dba-oracle.com/art_ext_tabs_spreadsheet.htm

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:

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], [], [], []