Laith Suheimat wrote:
> I am trying to resize an index that has become fragmented on one of the
> tables in a 7.1..5.2.3 database on AlphaVMS 6.2.
>
> The problem is that this index is actually the primary key constraint, and
> there are foreign key references to this PK on other tables.
>
> I disabled the constraint using DISABLE CONSTRAINT CASCADE to disable the
> FKs as well. The PK index was also dropped - I checked in DBA_INDEXES.
>
> However, when I came to recreate the PK index with the new size, using ALTER
> TABLE tab_name ADD CONSTRAINT PRIMARY KEY USING INDEX, Oracle complained
> that the PK already existed on the table.
>
> Thinking about it now, maybe I should try a DROP PRIMARY KEY pk_name
> CASCADE, instead of the disable.
>
Don't do that, unless you have the script(s) to recreate all the foreign
keys, because they are going to disappear in the process.
You must understand that the index is nothing else than a 'trick' to
implement the constraint - the constraint has a life of its own,
independently from the index which is just the implementation of the
'enabled' state. Disabling the constraint drops the index, but the
constraint is still here. If you want to recreate the index alone, you
just have to reenable the constraint. What you should do is
ALTER TABLE ...
ENABLE CONSTRAINT ...
USING INDEX ...
--
Regards,
Stéphane Faroult
Oriole Corporation
http://www.oriolecorp.com, designed by Oracle DBAs for Oracle DBAs
Received on Wed Apr 07 1999 - 20:52:36 CDT
