Oracle Blocking Session Checking Script

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.

« FND_TABLES Table Name, FND_COLUMNS Column Name

» Cannot Unlink: Permission Denied

Leave a Comment

2 Comments

#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 Reply

#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 Reply

Oracle Blocking Session Checking Script

Related Posts

Recent Posts