Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

Find Oracle blocking sessions

Oracle Tips by Burleson Consulting
May 26,  2009

Question:  I suspect that I have a locking problem and I need to understand how to query to locate blocking sessions in Oracle.  What views are used to find Oracle blocking sessions?

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:

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:

select
   blocking_session,
   sid,
   serial#,
   wait_class,
   seconds_in_wait
from
   v$session
where
   blocking_session is not NULL
order by
   blocking_session;

 
BLOCKING_SESSION        SID    SERIAL# WAIT_CLASS           SECONDS_IN_WAIT
---------------- ---------- ---------- -------------------- --------
             148        135      61521 Idle                              64

In this case, we find that session 148 is blocking session 135 and has been for 64 seconds. We would then want to find out who is running session 148, and go find them and see why they are having a problem.

For complete scripts for detecting blocking session, see the Oracle script collection.

 

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   Transa
ction   Exclusive            Exclusive            196702              5547

For complete scripts for blocking and waiting sessions, see the Oracle script collection.


 

 

��  
 
 
 
 

 
 
 

 
 
Oracle performance tuning software 
 
oracle dba poster
Oracle Linux poster
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright ? 1996 -  2014

All rights reserved by Burleson

Oracle ? is the registered trademark of Oracle Corporation.