Re: Recreate index because of ORA-600

I wouldn't second-guess Oracle support on the meaning of the parameters to ORA-00600.

The thing about ANALYZE INDEX is that it checks the internal consistency of the index blocks, it does NOT check that the rowid stored in the index actually point to rows that really exist in the table, and that may be your problem.

You could ask Oracle to tell you the event that can be used to dump the index tree count, then do a 'select count(*) from table' and see if the two sets of results match - this __might__ confirm for you that you have an index that is not consistent with the table it is pointing at, at least for the indexes where at least one column was declared non-null.

Actually I suppose you could do:

    select /*+ index (t, index_name) */ coiunt(*) from table t; to get the same effect, and perhaps to find the guilty index. Same restriction on non-null columns though.

--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

Vick wrote in message <7m29nu$bdf$1_at_nnrp1.deja.com>...
>Hi everyone...
>I called Oracle support for an ORA-600 error with arguments 2130, 271,
>32 and 3.
>
>I was told that argument 2130 means a corrupt index. I used ANALYZE
>TABLE schema.tablename VALIDATE STRUCTURE CASCADE; and everything came
>back valid... no errors.
>Then, just to be sure, I analyzed the indexes on the table. All valid.
>I told the support people this and they are telling me... drop the
>indexes and rebuild them.
>I don't think this is necessarily a bad thing, but... I'm paranoid.
>There are foreign keys in the indexes and I'm not really keen on
>screwing this up! I have a full table export with indexes, and a
>scheme owner export. (And a cold backup on tape!)
>
>Support is not completely sure that this action will "fix" my 600 error
>anyway...
>Any other suggestions would be appreciated!
>Tnx...
>Vick
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.
Received on Thu Jul 08 1999 - 10:13:21 CDT