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

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 2

My solution:

Problem

THIS DIDNT WORK WAS GETTING ERROR [0600]

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1904054272 bytes
Fixed Size                  2404024 bytes
Variable Size             570425672 bytes
Database Buffers         1325400064 bytes
Redo Buffers                5824512 bytes
Database mounted.
SQL> recover database
Media recovery complete.
SQL> alter database open
  2
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kcratr_nab_less_than_odr], [1],
[108], [615], [655], [], [], [], [], [], [], []`

HERE IS MY SOLUTION TO THE PROBLEM:

    SQL> Startup mount
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> shutdown abort
ORACLE instance shut down.
SQL>
SQL> startup mount
ORACLE instance started.

Total System Global Area 1904054272 bytes
Fixed Size                  2404024 bytes
Variable Size             570425672 bytes
Database Buffers         1325400064 bytes
Redo Buffers                5824512 bytes
Database mounted.
SQL> Show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      C:\APP\USER\ORADATA\ORACLEDB\C
                                                 ONTROL01.CTL, C:\APP\USER\FAST
                                                 _RECOVERY_AREA\ORACLEDB\CONTRO
                                                 L02.CTL
SQL> select a.member,a.group#,b.status from v$logfile a ,v$log b where a.group#=
b.group# and b.status='CURRENT'
  2
SQL> select a.member,a.group#,b.status from v$logfile a ,v$log b where a.group#=
b.group# and b.status='CURRENT';

MEMBER
--------------------------------------------------------------------------------

    GROUP# STATUS
---------- ----------------
C:\APP\USER\ORADATA\ORACLEDB\REDO03.LOG
         3 CURRENT


SQL> shutdown abort
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1904054272 bytes
Fixed Size                  2404024 bytes
Variable Size             570425672 bytes
Database Buffers         1325400064 bytes
Redo Buffers                5824512 bytes
Database mounted.
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 4234808 generated at 01/21/2014 18:31:05 needed for thread 1
ORA-00289: suggestion :
C:\APP\USER\FAST_RECOVERY_AREA\ORACLEDB\ARCHIVELOG\2014_01_22\O1_MF_1_108_%U_.AR

C
ORA-00280: change 4234808 for thread 1 is in sequence #108


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
C:\APP\USER\ORADATA\ORACLEDB\REDO03.LOG
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.

FINALLY IT WORKED:

Answer 3

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.

Answer 4

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 5

What is the full query?

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

Answer 6

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 7

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 8

I encountered this in a situation with a record type that involved USER_TAB_COLS. I had used VARCHAR2(128) for the field in the record that was supposed to hold USER_TAB_COLS.DATA_DEFAULT. Well, that's a LONG, for whatever reason, and while this mapping worked OK when I ran the code in a command window (PL/SQL Developer), it failed when I embedded the very same in an umbrella script that did all sorts of tasks. Once I set the data type of the record correctly to use an anchored type (USER_TAB_COLS.DATA_DEFAULT%TYPE) instead of VARCHAR2(128), the sp that does the work runs in either context. I was buffaloed by the fact that it worked 'stand-alone' but not when called from another script.
Here is the record type I was using:

TYPE PON_DICT_RECORD_TYPE Is RECORD(
    TABLE_NAME USER_TAB_COLS.TABLE_NAME%TYPE,
    PK         NUMBER(1),
    REQUIRED   NUMBER(1),
    COL_ORDER  NUMBER(3),
    COL_NAME   USER_TAB_COLS.COLUMN_NAME%TYPE,
    DATA_TYPE  USER_TAB_COLS.DATA_TYPE%TYPE,
    LENGTH     NUMBER(4),
    SCALE      NUMBER(2),
    DEF_VALUE  USER_TAB_COLS.data_default%TYPE, -- a LONG in U T C
    ID         NUMBER(1),
    FORCE_DEF  NUMBER(1),
    ACTIVE_PK NUMBER(1),
    NEW_COL_OLD_TABLE NUMBER(1),
    REPLACEMENT_KEY NUMBER(1)
    );

  TYPE PON_DICT_TABLE_TYPE IS TABLE OF PON_DICT_RECORD_TYPE;

  pdtab PON_DICT_TABLE_TYPE;

where you can see the definition of field DEF_VALUE is now an anchored type.

This was bizarre, I have to say. I would have expected it to fail in both contexts.

Answer 9

ORA-00600 generally means that there is something highly unexpected and it might be linked to the database corruption. The symptoms can be that query works or not depending on how it is formulated.

Life example:

• LOB field update went wrong for a particular row with id=<ID>
• The row is not visible with SELECT * FROM <table>
• But: SELECT * FROM <table> WHERE id=<ID> fails to execute and gives ORA-006000.

(Possible) Solution used to the above example

• export all accessible table contents
• delete table
• reimport the contents

Good Luck!