Re: Recreate index because of ORA-600

I believe that the validate structure option to the analyze command verifies that every entry in an index points to a table row and every row in the table has an index entry. This may be of some use.

In article <931447467.8249.1.nnrp-06.9e984b29_at_news.demon.co.uk>,   "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:
> 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.
>

--
Mark D. Powell -- The only advice that counts is the advice that  you follow so follow your own advice --

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Fri Jul 09 1999 - 08:58:40 CDT