Time to expand on the last post. As promised this will be about how to get the ORA-00600 information out of an external table for the alert log. We will use the same user and setup as we had in that post.

Lets first review how an ORA-00600 is represented in the alert log.

Tue Feb  1 02:09:44 2005
Errors in file /path/to/trace/file:
ORA-00600: internal error code, arguments: [1100],
[0x388F0D990], [0x382253888], [], [], [], [], []

The actual arguments does not matter, I took them from a forum post to just have an example. What we care about for what we’re doing here is that the format is one line with a date, one line with a reference to a trace file, and one line specifying the error code (ORA-00600) and the needed arguments (for Oracle support).

Thus, we’ll need to find the row with the ORA-00600 and match it up with the two previous rows. To do this we’ll return to my favorite SQL key word, the with clause. It lets us get all rows, get a generated unique id per row, and know that the rows we look at will be identical as we’re now using the same source. In theory, we could end up with the rows in different order if we joined the actual table with itself. Oracle does not guarantee the order you retrieve data in unless you order it, end we have nothing to order on here.

 with alert as (select rownum rn
                      ,text_line
                  from alert_log)
select a.text_line date_msg
      ,b.text_line file_msg
      ,c.text_line err_msg
  from alert a inner join alert b
    on a.rn = b.rn - 1
 inner join alert c
    on b.rn = c.rn - 1
 where c.text_line like '%ORA-00600%';

This gives us the alert log message in three columns. We can now process it any way we want. It could be used to create a report or generate an email with your ORA-00600 messages.

However, the actual ORA-00600 shows that we made a mistake when we created the table. It cuts off the text_line after a comma. That is not what we want, we want all data up to the end of the line. So we need to change the definition of the table to make sure we get all of that.

conn alertlog/alertlog
drop table alert_log;
create table alert_log (text_line varchar2(255))
organization external
  (type oracle_loader
   default directory bdump
   access parameters(fields terminated by x'10')
   location ('alert_ORCL.log'));

That’s better, the default delimiter of comma has been replaced with linefeed (ASCII 10) for UNIX platforms. Replace it with what you need. As long as it is a charcter that will never occur in a text line in your alert log, this will work fine.

Now that we have a select that gets all data for an ORA-00600 in each row it returns, it is easy to operate on that data. This model can of course be used to list other pieces of data from the alert log. One such use could be to report how long time it takes between log switches. That is left as an exercise for the reader.

My next post will show how to use this to create a RSS feed for your ORA-00600. That allows you to within minutes know when you have had an ORA-00600 occur and you get it delivered as a separate feed to your news/blog reader.