Drop Tablespace Failed with ORA-02429: cannot drop index used for enforce of uq

郑全

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

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;