ORA-04021 when dropping user cascade

Hi all !
Does anyone has ever encountered the error message :
ORA-04021: timeout occurred while waiting to lock object SYS.DBMS_SQL
while dropping cascade a user.
Is that possible that the user was not droppped but its objects were dropped after this message ?
I thought that if an Oracle error occured when perfoming a DML statement (like drop user), nothing really occured (the schema's objects were not dropped).
Any help would be appreciated ...

Hi,

If you have a login to Oracle Metalink try looking at the following link to see why your reaching a time out when trying to lock the object.

http://metalink.oracle.com/metalink/...&p_id=169139.1

Hope it helps.....

Cheers
Moff.

We've found out what was up and why the DBMS_SQL object was locked (and by whom).

But I'm still wondering how the schema's objects were dropped even if we received the Oracle error message "ORA-04021' when we launched the drop user statement ...

Any idea ?

I tried on my local DB by locking object exclusively by user A on the user B's one of the object.

User B has x objects, when i droped schema B as sysuser...all the objects were dropped except the locked object.

I am not quite sure about this..if I think logically then this may the resaon why

* DDL is also **DML** unlike explict commit is needed.
* While you drop user/schema, oracle will try to first drop user's objects..and I belive after each drop of object an implict commit is issued.

SO, till you got the error, the object's dropped have been reflected.

Possibly Jurij,Chris can help you understand better.

Abhay.

Thank you very much for your help ...
I thougth that Oracle DDL transactions were kind a "all or nothing", it means that if you receive an Oracle error message, nothing was accomplished ? Am I wrong ?

CASCADE
drops all objects in the user's schema before dropping the user

Now does it answer your Questions?