How do I fix an oracle Table/Index row count mismatch

Question

I just had an oracle9 database crash and it left me with a couple of .trc files. Some of them specified indexes that were out of kilter and i dropped and readded those indexes.

However, when I run:

ANALYZE TABLE TABLESPACE.TABLE VALIDATE STRUCTURE CASCADE;

I still get an error: ora_00900, sqlstate: 4200

This creates a .trc file with:

Table/Index row count mismatch
table 1172 : index 1250, 0
Index root = tsn: 9 rdba: 0x0240390b

What do I do with this information?

I found this link, however I'm not sure how to use it: http://www.freelists.org/post/oracle-l/Table-index-mismatch-trace-file,1

Answer 1

To be honest, with an error like this I would recommend opening an SR with Oracle - you want to make sure you don't lose your data!

Answer 2

The error says your indexes (perhaps not the ones you thought) are still bad.

From your link, if you run the query through SQL*PLUS it will ask for a rdba number. Enter the value from your error message '0x0240390b' (no quotes). This will return a file number and a block number.

SELECT dbms_utility.data_block_address_file(
         to_number(trim(leading '0' from
replace('&&rdba','0x','')),'XXXXXXXX')
       ) AS rfile#,
       dbms_utility.data_block_address_block(
         to_number(trim(leading '0' from
replace('&&rdba','0x','')),'XXXXXXXX')
       ) AS block#
FROM dual;

Next run the following query:

select owner, segment_name, segment_type 
from  dba_segments 
where header_file = <rfile#>
  and header_block = <block#>

This will give you the offending index to be dropped and recreated.