:

ORA-04021: timeout occurred while waiting to lock object

ORA-04021 timeout occurred while waiting to lock object

You’re here because you want to resolve the ORA-04021 timeout error error.

And below you will find one solution to that problem, so let’s dive straight in.

The Most Likely Cause…

The ORA-04021 timeout error is most likely caused by another session locking/using a package that you are attempting to execute.

Personally, I came across this error whilst trying to recompile a PL\SQL package in the database. It took its time to get to this error message – I don’t know what the timeout setting is but it sat there hanging at the SQL plus prompt for about 5 minutes, which I believe is the timeout value.

You can see other people who have been getting the same issue, here.

What does the ORA-04021 Error Message Mean?

  • Oracle uses a piece of memory which is shareable among all the database users called the Library Cache to maintain information about objects such as pl/sql packages, views, tables, etc.
  • The objects cannot be changed while they are in use, and therefore a lock must be taken out on an object in order to modify it; a library cache lock
  • A session will wait on the library cache pin or library cache lock if the object is already in use until a timeout occurs (usually 5 minutes with an ORA-04021 error)

How to Resolve the ORA 04021 Error?

What you do will depend on your environment but there are certain actions you should take to identify who is holding the lock then you will have enough information to make an informed decision as to what to do about the lock.

You may wait until the other process has finished using the object or perhaps you can kill the session, it all depends on your environment.

Below I would like to show you how you can identify which user(s) are causing the ORA-04021: timeout occurred while waiting to lock object error message.

  • V$ACCESS – You can use the V$ACCESS view to see which users have locks on which objects in your database
     SELECT SID, OWNER, OBJECT, TYPE
     FROM V$ACCESS
     WHERE OBJECT = 'object_name';
  • $SESSION_EVENT and V$SESSION_WAIT – use these views to see what Oracle wait events the session(s) are waiting on
     SELECT *
     FROM V$SESSION_EVENT
     WHERE SID = <sid>
     ORDER BY TIME_WAITED DESC;

     SELECT *
     FROM V$SESSION_WAIT
     WHERE SID = <sid>;
  • V$LOCKED_OBJECT – This view will also help you see who is locking the object

I have written another article about the v$locked_object view which you should check out quickly then come back to this page.

It might also be worth have a read of another article I wrote on blocking sessions in the Oracle database, which should give you some additional knowledge.

Other ORA-04021 Timeout Occurred Checks

  1. Check the package code for references to DB links which aren’t working correctly. Note here that the code may reference synonyms that point across DB links and not the DB link name directly so watch out for those.
  2. Speak with the developers and find out if they are compiling code. This could be the cause of the problem.
  3. DBA_DDL_LOCKS – use this view to see what DDL locks are on objects with the schema or against the object you are interested in
     SELECT *
     FROM DBA_DDL_LOCKS
     WHERE OWNER = <schema>;

Just quick before you go…

Must Read Oracle DBA BooksHopefully that helped you out and resolved your Oracle error. While you’re here, what steps are you taking to improve your Oracle knowledge?

Check out my latest post about which Oracle books are a must read for any serious Oracle DBA. Learn from the best…

 

Like it, share it...

Category: PL/SQL


Related Posts

Comments (3)

Trackback URL | Comments RSS Feed

  1. Richelle says:

    I see something really interesting regarding your internet site i absolutely saved to my bookmarks.

  2. Daniel says:

    small typo at the end

    SELECT *
    FROM DBA_DDL_LOCKS
    WHERE owner =

Leave a Reply

Your email address will not be published. Required fields are marked *