Drop Tablespace ORA-02429

when you delete a tablespace, you encounter a ORA-02429 Error:

Sql> drop tablespace TEST including contents and datafiles; Drop tablespace TEST including contents and datafiles * Line 1th Error: ORA-00604: Recursive SQL Level 1 error occurred ORA-02429: Unable to delete the index used to force a unique/primary key

Check TEST the index of the table space, found to be Normal type, not a unique index.

Sql> SELECT i.index_name, I.index_type, I.tablespace_name, I.table_owner 2 from User_indexes I 3 WHERE i.tablespace_name = ' TEST '; Index_name Index_type Tablespace_name Table_owner --------------         -----------            ------------------                  -------------- idx_test2_id NORMAL TEST SCOTT

 

  Check idx_test2_id is primary key index

Sql> SELECT c.constraint_name, C.constraint_type, C.table_name, C.index_name 2 from User_constraints C where index_name= ' idx_test2_id '; Constraint_name constraint_type table_name index_name -----------------------------  -----------------------  -------------------  --------------- pk_test2_id P TEST2 idx_test2_id

Delete the main health pk_test2_id after the table space TEST can be deleted

sql> ALTER TABLE TEST2 drop constraint pk_test2_id cascade; The table has changed. sql> drop tablespace TEST including contents and datafiles; The table space has been deleted.