Tag: primary key

Database, Errors and Bugs

ORA-02429 on Drop Tablespace

 , ,

Ok, so here is the error:

SQL> DROP TABLESPACE MY_TBS INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE MY_TBS 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

Hmm… Sounds pretty clear, right? So how to workaround it?

This is documented on MOS Drop Tablespace Failed with ORA-02429: cannot drop index used for enforcement of unique/primary key (Doc ID 1918060.1)

So, FIRST make sure this is not a problem for your application.
In case it is not, here is my step by step with some better scripting:

1) Disable the Constrains:

SQL> select 'ALTER TABLE '||owner||'.'||table_name||' DISABLE CONSTRAINT '|| constraint_name||';'
from dba_constraints
where (index_owner,index_name) in (select owner,index_name from dba_indexes
where tablespace_name='MY_TBS');

2) In case of PKs, you may need to disable it with CASCADE clause:

SQL> select 'ALTER TABLE '||owner||'.'||table_name||' DISABLE CONSTRAINT '|| constraint_name||' cascade;'
from dba_constraints
where (index_owner,index_name) in (select owner,index_name from dba_indexes
where tablespace_name='MY_TBS');

3) Drop the tablespace:

SQL> DROP TABLESPACE MY_TBS INCLUDING CONTENTS AND DATAFILES;

Hope it helps,
Cheers!