Re: howto detect which index is corrupted

Dominica Leung wrote:

> 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

I'd bet the interviewer had no idea either and was hoping you'd provide the answer they were looking for.

ORA-00600 is an unhandled internal Oracle error and the likelihood of figuring it out without opening a TAR reasonably close to nil.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
http://www.outreach.washington.edu/extinfo/certprog/aoa/aoa_main.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)