Chat now with support
Chat with Support

Spotlight on Oracle 10.10 - Getting Started Guide

Welcome to Spotlight Install Spotlight Start Spotlight Spotlight on Oracle Spotlight on Oracle Data Guard Spotlight on Oracle RAC Spotlight on Unix Spotlight on Windows Troubleshooting: Connection Problems

Shared Pool Lock Percentage Alarm

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:

SELECT s.sid,

       p.kglpnmod LockMode,

       p.kglpnreq LockRequest,

       b.kglnaown ObjectOwner,

       b.kglnaobj ObjectName

  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:

  • Ask the offending session to log off, OR
  • Kill the session to release the lock.


Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating