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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
