Oracle User Drop Error ORA-00604 ORA-01418

Scenario :

After Failed Import attempt of Oracle Schema I tried to drop the user (drop user XXX cascade).

But its failed with below error,

ORA-00604: error occurred at recursive SQL level 1

ORA-01418: specificed index does not exist

Environment:

Oracle 11gR2

Linux  2.6.18-128.el5

Possible Solution:

1. Log in Oracle as SYSDBA.

2. Purge Recyclebin

purge dba_recyclebin
purge recyclebin
purge user_recyclebin

3. Find the user id.

SELECT USER# FROM USER$ WHERE NAME = 'XXX';

4. Find the related objects (Optional).

SELECT * FROM OBJ$ WHERE OWNER# = USER#;

5. Remove the related objects.

DELETE FROM OBJ$ WHERE OWNER# = USER#;

6. Drop the user again.

DROP USER XXX CASCADE;

Issue Resolved. Enjoy.

Regards,

Md. Kaziul Islam Bulbul

Dhaka Bangladesh.