Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Table Locking Anomaly (Do I Believe OEM or Tom Kyte Scripts? ....)

RE: Table Locking Anomaly (Do I Believe OEM or Tom Kyte Scripts? ....)

From: Bobak, Mark <>
Date: Wed, 8 Feb 2006 11:28:25 -0500
Message-ID: <>

Hi Sam,  

Not sure what SQL OEM executes, but the Tom Kyte script looks ok. I like Steve Adams' script, which can be found at  

Also, as to exact rowid, if a session is waiting on a specific row, then the following columns in V$SESSION will tell you which one: ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, and ROW_WAIT_ROW#. However, remember that not all waits are row-level waits. Waits on enqueues of type other than TX will never be row-level waits, and even TX enqueues may not necessarily be row-level waits.  

Hope that helps,  


Mark J. Bobak 
Senior Oracle Architect 
ProQuest Information & Learning 

"There are 10 types of people in the world:  Those who understand
binary, and those who don't." 



[] On Behalf Of Sam Bootsma
Sent: Wednesday, February 08, 2006 10:58 AM
Subject: Table Locking Anomaly (Do I Believe OEM or Tom Kyte Scripts?



Friday afternoon, our trial tool (DBFlash) detected an enqueue problem.
I investigated the locking problem with both Oracle Enterprise Manager
and some scripts I lifted from Tom Kyte's book "Expert-One-on-One".  OEM
reported that user "A" was blocking user "B".  Tom Kyte's script
reported user "A" was blocking user "B" plus 15 additional users.
Although OEM did not show these 15 additional users as blocked, OEM did
show 1 of these 15 additional users was holding a row exclusive lock and
the remaining 14 users were holding row share locks. 


So who do I believe?  Is OEM that comes with Oracle 9i R2 known to be
buggy?  Or does the script from Tom Kyte need to be tweaked for Oracle
9i?  Or do they not contradict one another ... meaning the problem is
with me and my understanding is not correct?


Thanks for your input!  We are running Oracle on AIX 5.1.  Here
is the script from Tom Kyte:



    (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 v$lock a, v$lock b

where a.block = 1     -- means this lock is blocking another lock

  and b.request > 0

  and a.id1 = b.id1

  and a.id2 = b.id2

Another script from the DBFlash software agreed with the results from Tom Kyte's script: -- this script comes from DBFlash software SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess, id1, id2, lmode, request, type FROM V$LOCK WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM V$LOCK WHERE request>0) ORDER BY id1, request
Does anybody have a script that provides the exact row id(s) that a waiter is waiting on? Thanks Again, Sam Bootsma Oracle DBA George Brown College <> 416-415-5000 x4933


Received on Wed Feb 08 2006 - 10:28:25 CST

Original text of this message