One case of Drop user cascade failed ORA-00604 Processing

When you delete indexes, tables, tablespaces, users, and other database objects or users, you may encounter the following errors:

SQL> drop user Oracle cascade;
 
Drop user oracle cascade;
 
*
 
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
 
We know that the drop user with cascade will delete the tablespace to which it belongs along with other associated database objects. This also indicates that the objects to be deleted and other objects are associated with the primary and foreign key constraints, therefore, you need to query the constraints on the object and delete it.

SQL> select 'alter table' | owner | '.' | table_name | 'drop constraint' | constraint_name | ';'
From dba_constraints
Where constraint_type in ('U', 'P ')
And (index_owner, index_name) in
(Select owner, segment_name
From dba_segments
Where onwner = 'oracle ');
 
Alter table w. table_name drop constraint PK_ID;
 
Alter table x. table_name drop constraint FK_UID;
 
SQL> alter table w. table_name drop constraint pk_id;
 
Table altered
 
SQL> alter table x. table_name drop constraint fk_uid;
 
Table altered
 
SQL> drop user ORACLE cascade;
 
User dropped.
 
-- The user is deleted successfully.
 
You can drop user oralce cascade after you query the results and delete them. This error is a typical one. You can also see many extended problems on the Internet and record them.

Recommended reading:

ORA-01172, ORA-01151 error handling

ORA-00600 [2662] troubleshooting

Troubleshooting for ORA-01078 and LRM-00109

Notes on ORA-00471 Processing Methods

ORA-00314, redolog corruption, or missing Handling Methods

Solution to ORA-00257 archive logs being too large to store