oracle tips & tricks

Oracle Tips & Tricks

 
Thursday, 24 April 2008
Accessing Oracle Alert Log via SQL with External Tables
Starting in Oracle9i you can map external flat files to Oracle tables.
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], [], [], []

Labels: , ,

posted by popo @ 16:57  
0 Comments:
Post a Comment
<< Home
 
 
Search

Google
Previous Post
Category
Blogrush
Blogroll
Legal
Archives
Template by
Free Blogger templates