Show blocking sessions

Simple script for SQL*Plus to show blocking and waiting cases:


rem -------------------------------------------------------------------------
rem   Show users waiting for a lock, the locker and the
rem   SQL-Command they are waiting for a lock
rem   osuser, schema and PIDs are shown
rem -----------------------------------------------------------------------
rem
--
SET PAGES 24 LINES 100 FEED ON ECHO OFF TERMOUT ON HEAD ON
COLUMN OS_LOCKER FORMAT A15 TRUNC
COLUMN OS_WAITER FORMAT A15 TRUNC
COLUMN LOCKER_SCHEMA FORMAT A15 TRUNC
COLUMN WAITER_SCHEMA FORMAT A15 TRUNC
COLUMN WAITER_PID FORMAT A10
COLUMN LOCKER_PID FORMAT A10
COLUMN SQL_TEXT_WAITER FORMAT A100 WRAP
COLUMN DATABASE NOPRINT NEW_VALUE DATABASE
COLUMN DATUM_ZEIT NOPRINT NEW_VALUE DATUM_ZEIT
SET TERMOUT OFF ECHO OFF FEED OFF
SET TERMOUT ON
TTITLE CENTER 'Current Lock-Waits' SKIP 2
--
select  /*+ ordered
no_merge(L_WAITER)
no_merge(L_LOCKER) use_hash(L_LOCKER)
no_merge(S_WAITER) use_hash(S_WAITER)
no_merge(S_LOCKER) use_hash(S_LOCKER)
use_nl(O)
use_nl(U)
*/
/* first the table-level locks (TM) and mixed TM/TX TX/TM */
S_LOCKER.OSUSER OS_LOCKER,
S_LOCKER.USERNAME LOCKER_SCHEMA,
S_LOCKER.PROCESS LOCKER_PID,
S_WAITER.OSUSER OS_WAITER,
S_WAITER.USERNAME WAITER_SCHEMA,
S_WAITER.PROCESS WAITER_PID,
'Table lock (TM): '||U.NAME||'.'||O.NAME||
' - Mode held: '||
decode(L_LOCKER.LMODE,
0, 'None',           /* same as Monitor */
1, 'Null',           /* N */
2, 'Row-S (SS)',     /* L */
3, 'Row-X (SX)',     /* R */
4, 'Share',          /* S */
5, 'S/Row-X (SSX)',  /* C */
6, 'Exclusive',      /* X */
'???: '||to_char(L_LOCKER.LMODE))||
' / Mode requested: '||
decode(L_WAITER.REQUEST,
0, 'None',           /* same as Monitor */
1, 'Null',           /* N */
2, 'Row-S (SS)',     /* L */
3, 'Row-X (SX)',     /* R */
4, 'Share',          /* S */
5, 'S/Row-X (SSX)',  /* C */
6, 'Exclusive',      /* X */
'???: '||to_char(L_WAITER.REQUEST))
SQL_TEXT_WAITER
from
V$LOCK L_WAITER,
V$LOCK L_LOCKER,
V$SESSION S_WAITER,
V$SESSION S_LOCKER,
OBJ$ O,
USER$ U
where   S_WAITER.SID = L_WAITER.SID
and     L_WAITER.TYPE IN ('TM')
and     S_LOCKER.sid = L_LOCKER.sid
and     L_LOCKER.ID1 = L_WAITER.ID1
and     L_WAITER.REQUEST > 0
and     L_LOCKER.LMODE > 0
and     L_WAITER.ADDR != L_LOCKER.ADDR
and     L_WAITER.ID1 = O.OBJ#
and     U.USER# = O.OWNER#
union
select  /*+ ordered
no_merge(L_WAITER)
no_merge(L_LOCKER) use_hash(L_LOCKER)
no_merge(S_WAITER) use_hash(S_WAITER)
no_merge(S_LOCKER) use_hash(S_LOCKER)
no_merge(L1_WAITER) use_hash(L1_WAITER)
no_merge(O) use_hash(O)
*/
/* now the (usual) row-locks TX */
S_LOCKER.OSUSER OS_LOCKER,
S_LOCKER.USERNAME LOCKER_SCHEMA,
S_LOCKER.PROCESS LOCK_PID,
S_WAITER.OSUSER OS_WAITER,
S_WAITER.USERNAME WAITER_SCHEMA,
S_WAITER.PROCESS WAITER_PID,
'TX: '||O.SQL_TEXT SQL_TEXT_WAITER
from
V$LOCK L_WAITER,
V$LOCK L_LOCKER,
V$SESSION S_WAITER,
V$SESSION S_LOCKER,
V$_LOCK1 L1_WAITER,
V$OPEN_CURSOR O
where   S_WAITER.SID = L_WAITER.SID
and     L_WAITER.TYPE IN ('TX')
and     S_LOCKER.sid = L_LOCKER.sid
and     L_LOCKER.ID1 = L_WAITER.ID1
and     L_WAITER.REQUEST > 0
and     L_LOCKER.LMODE > 0
and     L_WAITER.ADDR != L_LOCKER.ADDR
and     L1_WAITER.LADDR = L_WAITER.ADDR
and     L1_WAITER.KADDR = L_WAITER.KADDR
and     L1_WAITER.SADDR = O.SADDR
and     O.HASH_VALUE = S_WAITER.SQL_HASH_VALUE
/
TTITLE OFF
COLUMN OS_LOCKER CLEAR
COLUMN OS_WAITER CLEAR
COLUMN LOCKER_SCHEMA CLEAR
COLUMN WAITER_SCHEMA CLEAR
COLUMN WAITER_PID CLEAR
COLUMN LOCKER_PID CLEAR
COLUMN SQL_TEXT_WAITER CLEAR
COLUMN DATABASE CLEAR
COLUMN DATUM_ZEIT CLEAR

Leave a Reply

Your email address will not be published. Required fields are marked *