The library cache, which is part of the shared pool in the Oracle instance, contains the definition of different objects, cursors, indexes, tables, functions, packages. When these objects are in use, they cannot be changed.

If someone is updating a table, you cannot change the definition of the table. If someone is using a procedure, you cannot compile the procedure. If someone is using an index, you cannot drop the index.


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

Why can’t you alter these objects, while in use?

These objects are locked in the library cache, by a mechanism based on library locks and library pins.

When a user wants to use an object, the session must obtain a library cache lock in null, shared, or exclusive mode on the objects, depending on the operation.

If you want to drop a table, you need an exclusive lock. This lock will prevent other sessions from executing the same operation, or other disruptive operations (dropping the object).

After the lock is acquired on the object, the session also must obtain a pin in a specific mode: null, shared, exclusive, depending on the operation.

A session that is waiting on an object already locked/pinned in the library cache, that is incompatible with the requested mode, will wait on events similar to library cache pin or library cache lock, for a limited time, at which point a timeout occurs.

Usually the timeout occurs after 5 minutes, and the waiting user will receive the ORA-4021 message TIMEOUT OCCURRED WHILE WAITING TO LOCK OBJECT.

You most likely want to troubleshoot and see who is holding a lock on your object. The problem is that the troubleshooting has to happen during the wait period, before the ORA-4021 occurs. I recommend that you start investigating when you see that your session is waiting, or hanging, and the session cursor is not returned to you right away.

Views that you can use to troubleshoot:


You can start your troubleshooting by checking regular blocking/locking issues.


Contact the Oracle team to analyze your situation. As general workaround you can stop and start the business events listeners, clearing cache from Oracle side. Then removed the locks on the tables. You'll need Oracle DBA help.

For more information visit: https://www.dbatrainings.com/

33 views0 comments

Recent Posts

See All

How to set cursor movement in oracle SQL

While you are working on SQL*Plus and RMAN on Linux environment and you did some mistake and you want to edit your query it won’t work by default. There is way to configure it. The rlwrap (readline wr

How to resolve Backspace key issue in sqlplus.

Backspace key does not work in sqlplus. It's not a issue form Oracle. This is a terminal type issue. echo $TERM stty -a and see if you have done anything special with the backspace key. I hope it’s wo