ORA-00604 or ORA-02429 Dropping Tablespace in Oracle Solution

ORA-00604: error occurred at recursive SQL level 1

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

you will get above error while you trying to dropping tablespace or droping index or constraint. here i have get this error while droping tablespace in oracle 10/11g. 

Solution:

Example.

Suppose you have TABLESPACE with name TESTTBL_I for indexes. and your are trying to drop this using below command:

SQL> drop tablespace TESTTBL_I including  contents and datafiles;

drop tablespace TESTTBL_I 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

you can also trying using below command to drop tablespace using cascade constraints option.

STEP-1:
SQL>drop tablespace TESTTBL_I including contents and datafiles cascade constraints;

if you still get ora-00604 or ora-02429 error after using cascade constraints option you need to do following steps.
STEP-2:
connect using sysdba privileges and check below command.

SQL> SELECT INDEX_NAME,TABLE_NAME,TABLESPACE_NAME FROM               DBA_INDEXES WHERE TABLESPACE_NAME= 'TESTTBL_I';
STEP-3:
if you found any index or object in this DBA_INDEXES view you need to drop or rebuild that particulate index on other tablespace.

SQL> ALTER INDEX INDEX_NAME REBUILD TABLESPACE TSNAME NOLOGGING;
or
SQL> DROP INDEX index_name;

Where INDEX_NAME= index name found in step2 & tsname is other than you need to drop.

Now Try to drop tablespace
SQL> drop tablespace TESTTBL_I including  contents and datafiles;
Tablespace dropped.

--please give your expert comments if any others.