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!
*********************************************************************2
This e-mail message is confidential, intended only for the named recipient(s) above
and may contain information that is privileged, attorney work product or exempt from
disclosure under applicable law. If you have received this message in error, or are
not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000
and delete this e-mail message from your computer, Thank you.
*********************************************************************2
