Corrupt index

[SANJAY_G]

Hi,

DB: Oracle 7.3.4.0
OS: Compaq Tru64Unix v4.0 D

I am getting ORA-600 while running query against one table. I got the following output after running "analyze table validate structure cascade"

Dump file /ora51/app/oracle/admin/cdsbp/udump/ora_9226.trc
Oracle7 Server Release 7.3.4.0.0 with the 64-bit option - Production
With the distributed option
PL/SQL Release 2.3.4.0.0 - Production
ORACLE_HOME = /ora51/app/oracle/product/7.3.4
System name: OSF1
Node name: puma.qntm.com
Release: V4.0
Version: 878
Machine: alpha
Instance name: cdsbp
Redo thread mounted by this instance: 1
Oracle process number: 26
Unix process pid: 9226, image: oraclecdsbp

*** 2001.08.20.15.38.51.931
*** SESSION ID:(10.1860) 2001.08.20.15.38.51.886
Table/Index row count mismatch
table 9949285 : index 9949298, 0
Index root = dba: 0x2c019935

How should I know from this trace file, which index to be rebuilt ? Or do I need to rebuild all indexes on this table??

Appreciate all the help.

Sanjay

[tycho]

Hi,

you can analyze the indexes on the table like this

ANALYZE INDEX indexname COMPUTE STATISTICS;

If the index is corrupt it will show up the same way as it did after the analyze table command.

I think all your indexes wil have to be rebuilded but I'm not sure.

Greetings,

Tycho

[marist89]

If you don't know which one is giving you the problem, I would rebuild them all.

[SANJAY_G]

Thanks Marist and Tycho,

I dropped and recreated all the indexes on that table but still getting the error. Does anybody know, how to find eaxctly which index is corrupt ??

Looking forward for some hints..

Thanks
Sanjay

[SANJAY_G]

Eureka !!!!!!

This is how you can find the exact corrupt index...

-Search the trace file for followin excerpts...

Block header dump: dba: 0x6401fb06
Object id on Block? Y
seg/obj: 0x4014 csc: 0x00.3493f469 itc: 3 flg: - typ: 2 - INDEX
fsl: 0 fnx: 0x0

-Take 4014 from seg/obj
-Convert this HEX value to Decimal.
-That will give you the object_id of the corrupt index.

In case if the table is of huge size (in my case, it is) and you can't afford to recreate all the indexes on the table this will help you to find exactly which index is corrupt.