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

While dropping the tablespace the following error occurred:

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL> DROP TABLESPACE TS_XXX_DATA_01 INCLUDING CONTENTS AND DATAFILES;

DROP TABLESPACE TS_XXX_DATA_01 INCLUDING CONTENTS AND DATAFILES

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

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

  Solution:

 

Use the command:

 

DROP TABLESPACE TS_XXX_DATA_01 INCLUDING CONTENTS AND DATAFILES cascade constraints;

 If you still face issues:

Use this query to find out where are the constraint exists within your tablespace:

 select * from dba_segments where tablespace_name= ‘TS_XXX_DATA_01’;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s