Re: Corrupt Primary Key index on table causing ORA-600 internal error

"Thiko" <biwombi_at_hotmail.com> wrote in message news:b8d3b40c.0408011124.3e3b4315_at_posting.google.com...
> Hi
>
> I have a corrupt PK index on a table column. It is a unique PK. It
> needs to be dropped and recreated to cure the corruption.
>
> The table is on a backup database which is in replication having
> records added to it to keep the two databases in sync.
>
> Am I correct in think that if I:
>
> 1. Suspend the replication so no data inconsistency happens on the
> table while the PK is disabled.
> 2. Disable the corrupt PK on the table - then the index will get
> dropped?
> 3. Enable the PK on the table - and the index will get rebuilt with
> the corruption gone?
> 4. Re-enable the replication.
>
> All will be fine? The table has over 9million rows so I realise it
> will take a while to re-enable the primary key but as is is a backup
> database in replication performance to users won't be affected.
>
> Many thanks!

Your point 2 is not generally true. Disabling or dropping a primary key or unique constraint will only cause the associated index to be dropped if that index happens to be a unique one. If it is a non-unique index, then the index will not be automatically dropped. A constraint created 'deferrable', for example, will be using a non-unique index. Check the index's uniqueness status in DBA_INDEXES, therefore. And also check DBA_INDEXES after the constraint disabling to make absolutely certain the index has gone.

Otherwise, your point 3 is correct. Re-enabling the constraint will cause a new index to be created, provided the original was indeed dropped.

Regards
HJR Received on Sun Aug 01 2004 - 14:29:45 CDT