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