Check for Blocking Locks

-------------------------------------------------------------
-- script: locks_blocking.sql
-- author: Mike Messina
-- desciption: Shows the blocking locks in a database
-- to trouble shoot sessions that appear to
-- be hung or possibly showing poor performance
-------------------------------------------------------------
SET PAGESIZE 500
SET FEEDBACK ONSET LINESIZE 200
SET TRIMSPOOL ON
SET ECHO OFF
set feedback off

-- Get Database Instance Name
column database_name noprint new_value i_name

SELECT UPPER(name) '_locks_blocking.log' database_name
FROM v$database ;

set feedback on

-- Spool to database instance file
SPOOL &i_name
select /*+ ORDERED */
l.sid,
s.serial#,
p.spid os_pid,
SUBSTR(s.username,1,15) username,
DECODE(l.request, 0, 'NO', 'YES') waiter,
SUBSTR(decode(l.type,
'MR', 'Media Recovery',
'RT', 'Redo Thread',
'UN', 'User Name',
'TX', 'Transaction',
'TM', 'DML',
'UL', 'PL/SQL User Lock',
'DX', 'Distributed Xaction',
'CF', 'Control File',
'IS', 'Instance State',
'FS', 'File Set',
'IR', 'Instance Recovery',
'ST', 'Disk Space Transaction',
'TS', 'Temp Segment',
'IV', 'Library Cache Invalidation',
'LS', 'Log Start or Switch',
'RW', 'Row Wait',
'SQ', 'Sequence Number',
'TE', 'Extend Table',
'TT', 'Temp Table',
l.type),1,30) lock_type,
SUBSTR(decode(l.lmode,
0, 'None',
1, 'Null',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share',
5, 'S/Row-X (SSX)',
6, 'Exclusive',
to_char(l.lmode)),1,15) mode_held,
SUBSTR(decode(l.block,
0, 'Not Blocking', /* Not blocking any other processes */
1, 'Blocking', /* This lock blocks other processes */
2, 'Global', /* This lock is global, so we can't tell */
to_char(l.block)),1,15) blocking,
SUBSTR(do.owner '.' do.object_name,1,50) object
from v$lock l,
v$session s,
v$process p,
v$locked_object lo,
dba_objects do
where l.sid = s.sid
and s.username is not null
and s.sid = lo.session_id (+)
and s.paddr = p.addr (+)
and lo.object_id = do.object_id (+)
and l.block <> 0 ;

SPOOL off