Wednesday, July 25, 2012

ORA-00600 error while querying a table

This happened recently in one of the setups. When user was querying a table for some data he was getting ORA-00600 [4000] ...We got this issue for resolution. Well this was the first time i came across an issue like this related to table corruption, so we to diagnose furthur we did a count(*) from table first.

1. Select count(*) from table;

ORA-00600: internal error code, arguments:[4000]...

Now we did and export of the table and it went fine

2. In this step we did VALIDATE STRUCTURE with CASCADE option.

SQL> Analyze table table name validate structure cascade;

ORA-00600: internal error code, arguments:[4000]...

 This again failed with same error

3. Now just to check whether its an index corruption or table corruption we again ran VALIDATE STRUCTURE but without cascade and it ran fine without any errors.

SQL> Analyze table table name validate structure ;

Table Analyzed.

4. From this we concluded that table was intact but corruption is with indexes, Further diagnosis revealed that  there was one index that too primary key

5. an online rebuild of the index was done

SQL > alter index index name rebuild online parallel 10;

This solved the problem and the error was gone