Logo Background

Oracle Blocking Session Checking Script

  • By on April 10, 2009 | 2 Comments

    Sometimes that when user complaining that they hit with error on “Record is currently being worked on by another user, Please try to update it later”. One of the possibility was due to Oracle table locking.

    Another possible problem was the user session was waiting for another user session to complete

    SELECT sid, serial#, username, STATUS, state, event, 
    blocking_session, seconds_in_wait, wait_time, action, logon_time
    FROM gv$session WHERE state IN ('WAITING') AND wait_class != 'Idle'
    AND event LIKE '%enq%'
    AND TYPE='USER'

    SID : 123
    USERNAME : APPS
    EVENT : enq: TX – row lock contention
    BLOCKING_SESSION : 254
    SECONDS_IN_WAIT : 8300
    WAIT_TIME : 0

    Looking at this SQL output, immediately we concludes that APPS’s session with SID 123 is waiting for a lock on a table and that that lock is held by SID 254 (BLOCKING_SESSION).

    To check what is running on both SID 123 and SID 254, we can find using the script below: –

    --Active Session Info
    SELECT b.sid, b.serial#, a.spid, b.program, a.username, b.osuser,
    b.machine, b.STATUS,b.logon_time, c.sql_text
    FROM v$process a, v$session b, v$sqltext c
    WHERE a.addr=b.paddr
    AND c.hash_value = b.sql_hash_value
    AND b.STATUS = 'ACTIVE'
    AND b.sid IN ('123', '254')
    ORDER BY a.spid, c.piece

    We can check this using OEM function “BLOCKING SESSION” as well, but this SQL script will come in handy if the OEM was not accessible.

    Previous
    Next
    » Cannot Unlink: Permission Denied
  1. #1 virtual dba
    March 30, 2010 8:55 pm

    There’s one SQL I used to detect the blocking session: –

    SELECT inst_id,substr(DECODE(request,0,’Holder: ‘,’Waiter: ‘)||sid,1,12) sess, id1, id2, lmode, request, type
    FROM GV$LOCK
    WHERE (id1, id2, type) IN
    (SELECT id1, id2, type FROM GV$LOCK WHERE request>0) order by id1

    The result will return SID of Holder and Waiter. In this case, Waiter will wait for Holder to release the lock. You can end the session of Holder in order to release the lock to Waiter.

    Post ReplyPost Reply
  1. #2 oracle
    September 23, 2010 8:54 pm

    Alternatively, you can run the SQL as below to check the Oracle DB blocking session.

    select (select username from v$session where sid=a.sid) blocker,
    a.sid,
    ‘ is blocking ‘,
    (select username from v$session where sid=b.sid) blockee,
    b.sid
    from
    (SELECT sid, id1, id2 FROM v$lock WHERE BLOCK = 1) a,
    (SELECT sid, id1, id2 FROM v$lock WHERE request > 0) b
    where a.id1 = b.id1
    and a.id2 = b.id2;

    Post ReplyPost Reply
Leave a Comment