Access your Alert Log via SQL with External Tables

Starting in Oracle9i you can map external flat files to Oracle tables.
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:
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/orcl/bdump';

create table
alert_log ( msg varchar2(80) )
organization external (
type oracle_loader
default directory BDUMP
access parameters (
records delimited by newline
)
location('alert_orcl.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], [], [], []