This event controls the concurrency between clients of the library cache. It acquires a lock on the object handle so that either:
One client can prevent other clients from accessing the same object
The client can maintain a dependency for a long time (for example, no other client can change the object)
This lock is also obtained to locate an object in the library cache.
Oracle needs Library Cache Lock and Library Cache Pin for the compilation and parsing of packages, procedures, functions, and other objects. This is to ensure that no one is using the object during compilation/parsing since changes are being made to the definition of the object.
Problem
This wait can occur when DDL is being executed on an object or one of its dependant objects. This causes a database object invalidation. Typically these are the object maintenance operations such as: ALTER, GRANT, and REVOKE. After object invalidation, Oracle tries to recompile the object at the time of the next access attempt. This may be a problem in a case where other sessions have pinned the object in the library cache because they are actively accessing the object. This is more likely to occur with more active users and with more complex dependencies (e.g. many cross-dependent packages or package bodies). In some cases, waiting for object recompilation may take hours, blocking all the sessions trying to access it.
Typical Scenarios
A DML operation that is hanging because the table which is accessed is currently undergoing changes (ALTER TABLE). This may take quite a long time depending on the size of the table and the type of the modification (e.g. ALTER TABLE MODIFY col1 CHAR(200) on millions of records).
The compilation of package will hang on Library Cache Lock and Library Cache Pin if some users are executing any procedure or function defined in the same package.
Sessions trying to access objects that have recently been modified in some way may have to wait while objects are recompiled.
Solution
If a session is hanging on this wait, it is possible to find its blocker and kill it. Refer to MetaLink Note: 122793.1 for details on how to find the blocker.
For a long-term fix, look at any processes or applications that might cause object invalidations and limit or time their execution for minimal impact. Be very careful with altering, granting or revoking privileges on database objects that frequently used PL/SQL is dependent on.
Comment