Re: Who is locking a record (not just a table)

In article <84v8h1$2583$1_at_news6.isdnet.net>,   "Michel Cadot" <micadot_at_netcourrier.com> wrote:
> v$session gives you the record you are *waiting*.
> Then you have to find the session that is holding
> the object (found with column id1 of v$lock).
> If no session are waiting for the row held, i don't
> know if it is possible to get the rowid held.
>
> Columns of v$session :
>
> ROW_WAIT_OBJ#
> Object ID for the table containing the ROWID specified in
ROW_WAIT_ROW#
>
> ROW_WAIT_FILE#
> Identifier for the datafile containing the ROWID specified in
ROW_WAIT_ROW#.
> This column is valid only if the session is currently waiting for
another
> transaction to commit and the value of ROW_WAIT_OBJ# is non-zero.
>
> ROW_WAIT_BLOCK#
> Identifier for the block containing the ROWID specified in
ROW_WAIT_ROW#. This
> column is valid only if the session is currently waiting for another
transaction
> to commit and the value of ROW_WAIT_OBJ# is non-zero.
>
> ROW_WAIT_ROW#
> The current ROWID being locked. This column is valid only if the
session is
> currently waiting for another transaction to commit and the value of
> ROW_WAIT_OBJ# is non-zero.
>
> --
> Have a nice day
> Michel
>
> S. Han <shan168_at_yahoo.com> a écrit dans le message :
> 84u8l5$ugn$1_at_nnrp1.deja.com...
> > Hi,
> >
> > Is there any view in Oracle8 to display which user is locking what
> > records? We are in the development of a VB6 application which
requires
> > to display userid who is currently locking the record. Such as:
> >
> > The reocrd is locked by USERID!
> >
> > V$session view can only provide object_id which doesn't go down to
ROW
> > level. Several users can access the same table but different rows.
> >
> > Any help will be appreciated!
> >
> > Steph
> >

Row locking information is stored in the Oracle data block so the only way to determine a row lock exists is to try to access the row. Oracle does not display row locks in any v$ table, but you can determine on a session level which session a blocked session is waiting on all the way to the lock holding session. You could assume that the utlimate blocker has the row lock in question, but it could be a resouce problem, i.e., lack of system resources.

You can look at dba_lock_blockers and its creation script, $ORACLE_HOME/rdbms/admin/catblock.sql plus Oracle provides a lock wait graph scrit in utllockt.sql which should be in the same directory.

--
Mark D. Powell -- The only advice that counts is the advice that  you follow so follow your own advice --

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Jan 05 2000 - 09:53:30 CST