DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: ORA-04021 when dropping user cascade

  1. #1
    Join Date
    Dec 2001
    Posts
    141

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

  2. #2
    Join Date
    Feb 2000
    Posts
    175
    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.

  3. #3
    Join Date
    Dec 2001
    Posts
    141
    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 ?

  4. #4
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  5. #5
    Join Date
    Dec 2001
    Posts
    141
    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 ?

  6. #6
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    CASCADE
    drops all objects in the user's schema before dropping the user

    Now does it answer your Questions?
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width