The Shared Pool Lock Percentage alarm is raised when one session holds a lock on object(s) in the Shared Pool for a prolonged period, and causes other sessions to wait on the same resource.
This "blocking lock" happens rarely. When it does occur, use a SQL query to find out which sessions are affected. In the case of a Library Cache Lock, use the query below:
FROM x$kglpn p, x$kglob b, v$session s
WHERE s.saddr = p.kglpnuse
AND p.kglpnhdl = b.kglhdadr
AND p.kglpnhdl IN (SELECT p1raw FROM v$session_wait
WHERE event LIKE '%library cache lock%')
A database administrator may either: