Cek Blocking Session

Berikut ini query untuk mengecek Blocking Session :

/* Formatted on 10/3/2011 4:59:51 PM (QP5 v5.163.1008.3004) */

SET PAGES 24 LINES 100 FEED ON ECHO OFF TERMOUT ON HEAD ON
COLUMN SID_LOCKER FORMAT A15 TRUNC
COLUMN SID_WAITER FORMAT A15 TRUNC
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)
       */
      S_LOCKER.SID SID_LOCKER,
       S_WAITER.SID SID_WAITER,
       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.SID SID_LOCKER,
       S_WAITER.SID SID_WAITER,
       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 SID_LOCKER CLEAR
    COLUMN SID_WAITER CLEAR
    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

Source : Internet