How to fix table counter / index mismatch of oracle table
I just had an oracle9 database crash and it left me a couple of .trc files. Some of them listed indexes that were out of order and I dropped and read those indexes.
However, when I run:
ANALYZE TABLE TABLESPACE.TABLE VALIDATE STRUCTURE CASCADE;
I still get the 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 should 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
a source to share
The error says that your indexes (maybe not the ones you thought) are still bad.
From your link, if you run the query through SQL * PLUS it will ask for the rdba number. Enter the value from your error message "0x0240390b" (without quotes). This will return the file number and 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;
Then 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 reset and recreated.
a source to share