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

Hello,  

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 9.2.0.6 on AIX 5.1. Here is the script from Tom Kyte:  

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

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

  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

sbootsma_at_gbrownc.on.ca <mailto:sbootsma_at_gbrownc.on.ca>

416-415-5000 x4933  

--
http://www.freelists.org/webpage/oracle-l