If you have encountered the problem of slow delete user cascade like me and your Oracle version is 10204, then you may already hitted a Oracle bug 6915130. For the details of this bug, you can check metalink bug note BUG:6915130 .
The symptom of this bug is when you drop user with cascade option, the dropping user statement runs very slow and look as if it hanged forever.When you run
select object_type,count(*) from dba_users where owner=’user_to_be_dropped'group by object_type,
you will see the no. of objects for the user remain unchanged for a very long time and
select sum(bytes) from dba_segments where owner=’user_to_be_dropped'
does not show any sign of cleaning user's segments.
I am not sure if this bug will occur in other versions of Oracle, though I seem to encounter similiar problem in other Oracle database. But Oracle offer solution to this bug in Metalink Note ID 798586.1. It suggested us to drop the objects owned by the user before we actually drop the user :-(. This fix works for my Oracle database and I think it should work for any versions of Oracle.
select object_type,count(*) from dba_users where owner=’user_to_be_dropped'
I am not sure if this bug will occur in other versions of Oracle, though I seem to encounter similiar problem in other Oracle database. But Oracle offer solution to this bug in Metalink Note ID 798586.1. It suggested us to drop the objects owned by the user before we actually drop the user :-(. This fix works for my Oracle database and I think it should work for any versions of Oracle.
Filed under:
ORA- Error