One case of DropusercascadefailedORA-00604ORA-02429 Processing

We know that the dropuser with cascade will delete the tablespace and other associated database objects, which also indicates that the object to be deleted exists between the primary and external objects.

We know that the drop user with cascade will delete the tablespace and other associated database objects, which also indicates that the object to be deleted exists between the primary and external objects.

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