Re: Recreate index because of ORA-600

Well, Mark,

When I read this I knew that one of us had to be wrong, then I checked the manual and found that we were both right:

Oracle 8i SQL Reference p4-164 - Validating Structures:

analyze index validate structure
'Note that this option does not confirm that each row in the table has an index entry or that each index entry points to a row in the table'

analyze table validate structure cascade 'Each entry in the index must identify a row in the table. The indexed column value in the index entry must match that of the identified row'. 'Each value fo the table's indexes column must match the indexed column value of an index entry. The matching index entry must also identify the row in the table by the correct ROWID'.

So the answer is to validate the table structure with the cascade option - though I suspect this might take quite a long time to do for a big table.

--

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

markp7832_at_my-deja.com wrote in message <7m4v69$auo$1_at_nnrp1.deja.com>...
>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.
Received on Fri Jul 09 1999 - 09:16:51 CDT