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

  • From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
  • To: <sbootsma@xxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 8 Feb 2006 11:28:25 -0500

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:
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." 



From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Sam Bootsma
Sent: Wednesday, February 08, 2006 10:58 AM
To: oracle-l@xxxxxxxxxxxxx
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


WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM V$LOCK WHERE

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

sbootsma@xxxxxxxxxxxxx <mailto:sbootsma@xxxxxxxxxxxxx> 

416-415-5000 x4933


FN:Bobak, Mark
ORG:ProQuest Information and Learning;Database Group
TITLE:Software Architect, Sr-TPD
ADR;WORK:;ECC4-N01E04;1400 Eisenhower Parkway

Other related posts: