In this Document
Goal
Solution
APPLIES TO:
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.
*** Checked for relevance on 22-Mar-2016 ***
GOAL
Dropping a tablespace fails with below error:
SQL> DROP TABLESPACE <tablespace_name> INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE <tablespace_name> 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
Find the constraint name for the unique/primary key, disable the constraint and drop the tablespace again.
Steps:
=====
1) Execute below query to find the constraint name:
SQL> select owner, constraint_name,table_name,index_owner,index_name
from dba_constraints
where (index_owner,index_name) in (select owner,index_name from dba_indexes
where tablespace_name='<tablespace_name>');
2) Disable the constraint:
SQL> ALTER TABLE <owner>.<table_name> DISABLE CONSTRAINT <constraint_name>;
3) Drop the tablespace:
SQL> DROP TABLESPACE <tablespace_name> INCLUDING CONTENTS AND DATAFILES;