Answer:  You can query the dba_blockers and dba_waiters views to locate blocking sessions, but you can also get this information from v$lock and v$session.

Also see these related notes on finding Oracle blocking sessions:

• Find blocking sessions with v$session

• Find the data block for a blocking session

• Oracle RAC blocking sessions

See what session is blocking other sessions

Blocking sessions are a problem for the DBA and we need a way to find them so we can deal with them. Blocking sessions occur when a session issues an insert, update or delete command that changes a row.

When the change occurs, the row is locked until the session either commits the change, rolls the change back or the user logs off the system. You can see where problems might occur, for example a user might make a change and then forget to commit it and leaves for the weekend without logging off the system.

We can use this query to find these nasty blocking sessions. We use our old friend, v$session to find the blocking session, and also a list of sessions locked by that session.

Here is a query that gives us a list of blocking sessions and the sessions that they are blocking:

Killing an Oracle blocking session

This script will query the  dba_lock and dba_blockers view to locate a blocking session:

COLUMN username         FORMAT a10 HEADING 'Holding|User'
COLUMN session_id                         HEADING 'SID'
COLUMN mode_held        FORMAT a20 HEADING 'Mode|Held'
COLUMN mode_requested   FORMAT a20 HEADING 'Mode|Requested'
COLUMN lock_id1         FORMAT a20 HEADING 'Lock|ID1'
COLUMN lock_id2         FORMAT a20 HEADING 'Lock|ID2'
COLUMN type                               HEADING 'Lock|Type'
SET LINES 132 PAGES 59 FEEDBACK OFF ECHO OFF
START title132 'Sessions Blocking Other Sessions Report'
SPOOL rep_out\&db\blockers
SELECT
      a.session_id,
      username,
      type,
      mode_held,
      mode_requested,
      lock_id1,
      lock_id2
FROM
      sys.v_$session b,
      sys.dba_blockers c,
      sys.dba_lock a
See code depot for full script
WHERE
      c.holding_session=a.session_id and
      c.holding_session=b.sid
/
SPOOL OFF
PAUSE press enter/return to continue
CLEAR COLUMNS
SET LINES 80 PAGES 22 FEEDBACK ON ECHO ON

Example script to report on blocking locks

rem
rem FUNCTION: Report on sessions waiting for locks
rem
COLUMN busername FORMAT A10 HEADING 'Holding|User'
COLUMN wusername FORMAT A10 HEADING 'Waiting|User'
COLUMN bsession_id HEADING 'Holding|Sid'
COLUMN wsession_id HEADING 'Waiting|Sid'
COLUMN mode_held FORMAT A20 HEADING 'Mode|Held'
COLUMN mode_requested FORMAT A20 HEADING 'Mode|Requested'
COLUMN lock_id1 FORMAT A20 HEADING 'Lock|Id1'
COLUMN lock_id2 FORMAT A20 HEADING 'Lock|Id2'
COLUMN type HEADING 'LOCK|TYPE'
SET LINES 132 PAGES 59 FEEDBACK OFF ECHO OFF
START TITLE132 'Processes Waiting on Locks Report'
SPOOL rep_out/&db/waiters
SELECT
       holding_session bsession_id,
      waiting_session wsession_id,
      b.username busername,
      a.username wusername,
      c.lock_type type,
      mode_held, mode_requested,
      lock_id1, lock_id2
FROM
sys.v_$session b, sys.dba_waiters c, sys.v_$session a
WHERE
See code depot for full script
c.holding_session=b.sid and
c.waiting_session=a.sid
/
SPOOL OFF
PAUSE press enter/return to continue
CLEAR COLUMNS
SET LINES 80 PAGES 22 FEEDBACK ON ECHO ON
TTITLE OFF

Example script to report on blocking and waiting sessions.

Example output from the blockers report is shown for a simple lock situation.

Date: 02/08/00                                                                            Page:   1
Time: 07:01 AM                Sessions Blocking Other Sessions Report                     SYSTEM
                                       aultdb database
 

          Holding    Lock       Mode         Mode                 Lock                 Lock
      SID User       Type       Held         Requested            ID1                  ID2
--------- ---------- ---------- ------------ -------------------- -------------------- ------------
        9 LABUSER    USER       Row-X (SX)   None                 2821                 0                                   
        9 LABUSER    USER       Exclusive    None                 196702               5547                                

Example Oracle Blockers Report

The output for the same lock situation from the waiters report is shown in Listing 30.

Date:02/08/00
                                          Page:   1
Time: 07:01 AM                   Processes Waiting on Locks Report
SYSTEM
                                     aultdb database

  Holding   Waiting Holding    Waiting     Lock         ModMode                 Lock                 Lock
      SID       SID User       User        Type         Held                 Requested            ID1                  ID2
--------- --------- ---------- ----------- ------------ -------------------- -------------------- -------------------- -----------
        9        13 LABUSER    LABUSER2   Transaction   Exclusive            Exclusive            196702              5547