find what user has a row locked for update??

[Guest]

Is there a way to find out what user has a lock on a record? I am
performing a lock on a record by doing a for update nowait in my select
statement. I want to get down to the row that is locked in that table.
I have found that I can see what user has a record locked on a table
by executing:
select
oracle_username,
os_user_name,
locked_mode,
object_name,
object_type
from
v$locked_object a,dba_objects b
where
a.object_id = b.object_id;

Is there a way to find out which record that user is locking? Say an
ID field.
Thanks.

Jeff Chirco

[K Gopalakrishnan]

Jeff:

It would be quite difficult to get the details without block dumps as
the lock information is kept at the data block level and oracle does
not maintain any external record (lock table) for the TX locks. But if
the session is waiting for the row, which is already locked by some
other user, sometimes that information is visible in V$SESSION (FILE#,
BLOCK# and ROW# for ROW_WAITs)

Best Regards,
K Gopalakrishnan
Co-Author: Oracle Wait Interface, Oracle Press 2004
http://www.amazon.com/exec/obidos/tg/detail/-/007222729X/

[DA Morgan]

jeffchirco@gmail.com wrote: Is there a way to find out what user has a lock on a record? I am performing a lock on a record by doing a for update nowait in my select statement. I want to get down to the row that is locked in that table. I have found that I can see what user has a record locked on a table by executing: select oracle_username, os_user_name, locked_mode, object_name, object_type from v$locked_object a,dba_objects b where a.object_id = b.object_id; Is there a way to find out which record that user is locking? Say an ID field. Thanks. Jeff Chirco

This may not be what you want but look at:

$ORACLE_HOME/rdbms/admin/catblock.sql
Creates views that dynamically display lock dependency graphs

$ORACLE_HOME/rdbms/admin/userlock.sql
Routines that allow the user to request, convert and release locks.

$ORACLE_HOME/rdbms/admin/utllockt.sql
Prints the sessions in the system that are waiting for locks, and the
locks they are waiting for.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)