Re: which row is locked?

In article <913hs8$9qq$1_at_mailint03.im.hou.compaq.com>,   "david spaisman" <david.spaisman_at_compaq.com> wrote:
> Mark:
>
> So which data dictionary table would I need to query to get thedata
 block
> transaction work area
> when there is no waiting on locked rows?
>
> Any help would be appreciated. Thanks.
>
> David
>
> Mark D Powell wrote in message <913a1r$pf7$1_at_nnrp1.deja.com>...
> >In article <9130lo$h2r$1_at_nnrp1.deja.com>,
> > steveee_ca_at_my-deja.com wrote:
> >> Hi David,
> >>
> >> If you're trying to find which row is causing contention, you can
 use
> >> V$SESSION..look at these columns:
> >>
> >> ROW_WAIT_OBJ#
> >> ROW_WAIT_FILE#
> >> ROW_WAIT_BLOCK#
> >> ROW_WAIT_ROW#
> >>
> >> I hope this helps.
> >>
> >> Steve
> >>
> >> In article <912q5k$5in$1_at_mailint03.im.hou.compaq.com>,
> >> "david spaisman" <david.spaisman_at_compaq.com> wrote:
> >> > Hello:
> >> >
> >> > The application I am working with runs on Oracle 8.1.6.00.
> >> >
> >> > I have seen several scripts which can tell me which object is
 locked
 by
> >> > user. What would be more helpful would be:
> >> >
> >> > how can I tell which row is locked by a particular process?
> >> >
> >> > Does any one know how I can tell which row for an object is
 locked
 by
 which
> >> > user?
> >> >
> >> > Any information you can provide will be greatly appreciated.
 Thanks.
> >> >
> >> > David Spaisman
> >> >
> >The v$session columns will contain rowid information only if the
> >session is waiting on a locked row. The columns will be empty if the
> >session is waiting on a resource.
> >
> >View v$lock will show all objects locked by a session, but there is
 no
> >place in the SGA where Oracle keeps row level lock information for
 all
> >row level locks held by the locking session. Oracle keeps row level
> >lock information in the Oracle data block transaction work area so
 the
> >only way to see if a row, which has no waiting sessions, is locked is
> >to query it.
> >
> >--
> >Mark D. Powell -- The only advice that counts is the advice that
> > you follow so follow your own advice --
> >

As I said "Oracle keeps row level lock information in the Oracle data block transaction work area so the only way to see if a row, which has no waiting sessions, is locked is to query it."

There is no dictionary or v$ table that you can query for this information. You have to attempt to retrieve the row for update with the nowait option and check the return code. This is not a practical way to see what rows an application has locked except maybe under limited circumstances.

--
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.