Oracle ORA-00600

Question

I have SQL SELECT statement that returns:

    Error: ORA-00600: internal error code, arguments: [qerpfAllocateR], [], [], [], [], [], [], []

If I narrow my results by adding one more condition in WHERE clause everything is ok.

Anyone knows what is happening?

EDIT:

    select * from ( select tbl1.col1, ..., tbl1.points
    from table1 tbl1, table2 tbl2
    where tbl1.tbl2FK = tbl2.PK and
          tbl2.col1 = someNumber and
          tbl1.dateColumn = to_date('27-10-2008','dd-mm-yyyy')
    order by tbl1.points desc ) s where rownum <= 3

EDIT2:

My DB admin suggested solution that works:

select * from (select rank() over (order by tbl1.points desc) rank,
                  tbl1.col1, ..., tbl1.points
           from table1 tbl1, table2 tbl2
           where tbl1.tbl2FK = tbl2.PK and
                 tbl2.col1 = someNumber and
                 tbl1.dateColumn = to_date('27-10-2008','dd-mm-yyyy')) s
     where s.rank <= 3

Answer 1

What is the full query?

http://www.orafaq.com/wiki/ORA-00600 suggests you should report the error to oracle.

Answer 2

I have seen errors like this when there are XMLTYPE columns in the table, using PL/SQL Developer. It wouldn't happen if I had PL/SQL Developer create the query skeleton for me because it would add some syntax to the XMLTYPE column request, I can't remember exactly what.

Answer 3

Something here regarding a similar issue - does your select use joins ?

Answer 4

The ORA-0600 error indicates an internal error in Oracle itself. You're most likely hitting an Oracle bug.

If you go to http://metalink.oracle.com, you can look up the exact bug, if it is known, by pasting the "arguments" and full stack trace into the ORA-0600 lookup tool. See metalink note 153788.1.

If there's a known bug with that description, it's likely that there's already a patch available for you to download and install. If it's not a known bug, contact support.

Answer 5

Good luck with getting support from Oracle...

Seriously though, every time I hit this problem, rearranging the query a little bit usually helps. Maybe fiddling around with the indexes a bit.

Answer 6

These bugs are usually related to the optimizer. I think even the smallest change in the query like switching the order of table1 and table2 in the FROM clause might force the optimizer to choose a different plan that will not encounter this error.

Answer 7

ORA-00600 basically means you've crash the oracle server (not the instance, just the server servicing your request).

There will almost always be a trace file in your bdump location. This likely wont be a lot of help you to, but will be very helpful to oracle support.

This is generally caused by an oracle bug, and from experience, there isn't a lot you can do about them except raise a SR via metalink (this is the recommended solution from Oracle). They will try to replicate the issue and with any luck, if its a bug it will eventually find its way into a patch.

In the immediate term though (eg, days - months) the main realistic solution is work around it.

While raising the SR doesn't really do alot to help you and can be a frustrating experience, its worth doing, as it might save someone else time once the bug is fixed.