library cache pin &lock


  SELECT 'kill -9 '||p.spid
  FROM v$session s,v$process p
WHERE s.saddr IN (
          SELECT kgllkses
            FROM x$kgllk lock_a
           WHERE kgllkreq = 0
             AND EXISTS (
                    SELECT lock_b.kgllkhdl
                      FROM x$kgllk lock_b
                     WHERE kgllkses in
                              (SELECT saddr
                                 FROM v$session_wait w, v$session s
                                WHERE w.event = 'library cache lock'
                                  AND s.SID = w.SID
                                                                  AND w.state='WAITING')
                       AND lock_a.kgllkhdl = lock_b.kgllkhdl
                       AND kgllkreq > 0))
   AND s.TYPE = 'USER'
   and s.paddr=p.addr

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: