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.
CAUSE
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:
V$ACCESS DBA_DDL_LOCKS V$LOCKED_OBJECT
You can start your troubleshooting by checking regular blocking/locking issues.
SOLUTION
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/