howto detect which index is corrupted

Hi All,

  I went to an oracle DBA job interview the other day. They ask this question.

1. When application running a SQL statement and get ORA-600 errors.

From the argument of ORA-600, the DBA go and look up and find out "SQL can't be execute due to index corrupted".

We know this SQL use 10 tables and
15 indexes.
We have a UDUMP trace file. But look encrypted.

How would you find out which indexes is corrupted?

I can't answer it immediately, I told interviewer, I pass on this question. But I am still curious even after I went home. I keep thinking about it.

Now, I am thinking you could
use 'Analyze table validate structure...' to verify. But I read Oracle document, it says it will LOCK the table.

The interviewer said Oracle probably has tools to decrypte the dump file and find out which indexes.

To me, I was thinking writing simple SQL statement to access those indexes. But
interwiewer said "NO ALLOW".

I wonder how to find out.
without using "Analyze table validate..".

Thank you in advance, any ideas/suggestion are welcome!! I am still very curious about it.

Dominica Leung Received on Sat Aug 30 2003 - 19:49:56 CDT