drop user error ORA-00604 ORA-01418

Evironment

Project: Zenni

Date: 2013-05-18

OS: Centos Linux

Oracle Version: Oracle Database 11g Release 11.2.0.1.0 - Production

 

Key Words

drop user ORA-00604 ORA-01418

Symptoms

When drop user use the follow sql the server reject the request.

"drop user username cascade"

ORA-00604: error occurred at recursive SQL level 1

ORA-01418: specified index does not exist

 

Cause

The metadata is error. Some indexes exists, but the tables which the indexes depend on are not exists. So we can't drop the index by nomal sql " Drop index index_name".

We named these indexes "problem index".

Solution

Modify the metadata to delete the "problem index" by the follow step:

1. select user_id from dba_users where username='user"
2. select * from obj$ where owner#='user_id';
3. delete obj$ where owner#='user_id';
4. commit;
5. restart the database.