select * from V$access where type='PROCEDURE'

V$ACCESS displays information about locks that are currently imposed on library cache objects. The locks are imposed to ensure that they are not aged out (被清除)of the library cache while they are required for SQL execution.

select name
from v$db_object_cache  where   type='PROCEDURE' and LOCKS >0 and  PINS >0

--This view displays database objects that are cached in the library cache. Objects include tables, indexes, clusters, synonym definitions, PL/SQL procedures and packages, and triggers.

LOCKS   NUMBER Number of users currently locking this object
PINS       NUMBER Number of users currently pinning this object 
 
library cache pin和library cache lock都是两种数据结构来进行shared pool的并发控制。pin和lock都可以看作是一种锁，Lock比pin具有更高的级别。locks/pins会在SQL语句执行期间一直保持,在结束的时候才释放。

Lock在handle上获得,在pin一个对象之前,必须首先获得该handle的锁定.锁定主要有三种模式: Null,share,Exclusive.
 在锁定了Library Cache对象以后,一个进程在访问之前必须pin该对象.同样pin有三种模式,Null,shared和exclusive.
只读模式时获得共享pin,修改模式获得排他pin.

透彻的认识：

An Oracle instance has a library cache that contains the description of different types of objects e.g. cursors, indexes, tables, views, procedures, .. Those objects cannot be changed when they are used. They are locked by a mechanism based on library locks and pins. A session that need to use an object will first acquire a library lock in a certain mode (null, shared or exclusive) on the object, in order to prevent other sessions from accessing the same object (e.g. exclusive lock when recompiling a package or view) or to maintain
the object definition for a long time. Locking an object is sometimes referred as the job to locate it in the library cache and lock it in a certain mode. If the session wants to modify or examine the object, it must acquire after the lock also a pin in a certain mode (again null, shared or exclusive).

Each SQL statement that want to use/modify objects that are locked or pinned and whose lock/pin mode is incompatible with the requested mode, will wait on events like 'library cache pin' or 'library cache lock' until a timeout occurs. The timeout normally occurs after 5 minutes and the SQL statement then ends with an ORA-4021. If a deadlock is detected, an ORA-4020 is given back.