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
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.