library cache pin & library cache lock

Once there was an error on one of our production database. I saw some process awaiting for "library cache lock". One process was in "library cache pin' state. This situation was expected during table partition movement operation.

As I understood it was a consequences of invalid package which became invalid due to "alter table XXX move partition". Session 388 was getting library cache pin when he or some system process had been tried to recompile invalid object during partition movement. The others were waiting for 388 process to get a lock. After partition had been moved successfully package was recompiled and the queue disappeared.



Some from Oracle doc:

•  Oracle attempts to recompile an invalid object dynamically only if it has not been replaced since it was detected as invalid. This optimization eliminates unnecessary recompilations.
•  Whenever you create a table, index, and view, and then drop the table, all objects dependent on that table are invalidated, including views, packages, package bodies, functions, and procedures.
• If the referenced table is altered, then the dependent procedure is invalidated.
• If the base table of the referenced view is altered, then the view and the dependent procedure are invalidated.
• If the referenced standalone procedure is replaced, then the dependent procedure is invalidated.
• If the body of the referenced package is replaced, then the dependent procedure is not affected. However, if the specification of the referenced package is replaced, then the dependent procedure is invalidated. This is a mechanism for minimizing dependencies among procedures and referenced objects by using packages.
• Whenever you create a table, index, and view, and then drop the table, all objects dependent on that table are invalidated, including views, packages, package bodies, functions, and procedures.

SELECT * FROM vtest; 
and 
ALTER VIEW vtest compile; 
have the same effect