Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> Locked Rows in the DB ... the search continues ...
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
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).Received on Thu Aug 01 2002 - 10:53:30 CDT
- text/plain attachment: ESPN_Disclaimer.txt
![]() |
![]() |