RE: Locked Rows in the DB ... the search continues ...

From: Post, Ethan <Ethan.Post_at_ps.net>
Date: Thu, 01 Aug 2002 08:58:30 -0800
Message-ID: <F001.004A8FDF.20020801085830@fatcity.com>

Have you tried Steve's script enqueue_locks.sql (I think). It is on his site and does a really good job. You will will need to take the Object Id and look up the object but I use it all the time as my primary means of locating blocks.

Ethan Post
perotdba (AIM), epost1 (Yahoo)

---

-----Original Message-----
Sent: Thursday, August 01, 2002 10:54 AM To: Multiple recipients of list ORACLE-L

Hi all,

I am trying to find who is locking rows on a (specific) table .... so far I have come up with following ...

SELECT  a.sid


       ,a.serial#

       ,a.osuser

       ,a.username 

       ,a.ROW_WAIT_OBJ# object_id

       ,b.object_name

       ,dbms_rowid.rowid_create(1, a.row_wait_obj#,

a.ROW_WAIT_FILE#,a.ROW_WAIT_BLOCK#,a.ROW_WAIT_ROW#) wait_on_rowid
  FROM v$session a, db$objects b
 WHERE a.ROW_WAIT_OBJ# <> -1
   AND a.ROW_WAIT_OBJ# = b.object_id
 ORDER BY 6, 7 

which tells me that the displayed sessions are *waiting* for the specified rowid. The table db$objects is a copy of dba_objects. Joining with dba_objects is slow, so I have created a materialized view called db$objects (gets refreshed overnight).

Now reading Steve Adams book, he mentions on pp46 that (in summary)

' ... The reason for waiting is that tx has modified a datablock., and the waiting session needs to modify some part of that data block. In such cases ROW_WAIT column of v$session can be useful in identifying the db object, file, block numbers, and even the row number in case of row lock. The view v$locked_object can then be used to obtain session information for the sessions holding DML locks on the crucial database objects."

Now I can also select from v$locked_object to see who is holding locks on a specific table.

Now my question is, how do I put 2 and 2 together to display a nice output something like ...

User "A" is waiting for row "R" in object "O", which is being blocked by user "B" in session 'bsid".

Am I on track ... or I should have taken the previous exit??

Also does information in v$session pertains to 'waiting for rowid' or 'I have this rowid locked' or both?

Thanks for your help in advance
Raj

---

Rajendra Jamadagni MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.

QOTD: Any clod can have facts, but having an opinion is an art!

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Post, Ethan
  INET: Ethan.Post_at_ps.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Thu Aug 01 2002 - 11:58:30 CDT