Oracle-L: Locked Rows in the DB ... the search continues ...

From: Jamadagni, Rajendra <Rajendra.Jamadagni_at_espn.com>
Date: Thu, 01 Aug 2002 07:53:30 -0800
Message-ID: <F001.004A8D5B.20020801075330@fatcity.com>

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: Jamadagni, Rajendra
  INET: Rajendra.Jamadagni_at_espn.com

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).



text/plain attachment: ESPN_Disclaimer.txt

Received on Thu Aug 01 2002 - 10:53:30 CDT