Wednesday, December 9, 2009

Ora-02429 cannot drop index used for enforcement of unique/primary key

There are multiple reasons why Ora-02429 error may happen. I encountered this problem while deleting tablespace dedicated to indexes in the product we are using.

One may want to try following options:

  1. Use "cascade constraints" e.g. drop tablespace [name of ts] cascade constraints;
  2. check what objects are in this tablespace and move these to some where else and drop the tablespace.
  3. alter index [indexname] rebuild [tablespacename]; (this is because, rowid's will change when a table is moved/re-org)
  4. If you are trying to delete tablespace which is still referenced by any user; you need to make sure either users are removed (recursively) and that there are no data related to any user in there. Delete the user (Schema) with all the objects and it should free up any references.

For me the last option worked!

No comments:

Post a Comment