Re: Table Locks

Alan Davey wrote:
>
> Thanks Rachel.
>
> I spent the train ride reading the chapters on Instance Tuning and Dynamic Performance Views hoping to find something, but no such luck. I learned a lot of other useful things though, so it wasn't a waste of time.
>
> Jacques, v$locked_object shows the table, but I already knew which table was locked. I was hoping to find the offending SQL statement.
>
> Have a great weekend everyone.
>
> Regards,
> --
>
> Alan Davey
> adavey_at_competitrack.com
> 212-604-0200 x106
>
> On 8/29/2002 10:43 PM, Rachel Carmichael <wisernet100_at_yahoo.com> wrote:
> >I'm not sure it's possible to find the locking SQL and SID once the
> >session issues other SQL statements.
> >
> >I spent a lot of time a few years back attempting to find it, without
> >success. I got the people at both Platinum Technology and Savant
> >(yes,
> >I'm showing my age here) to try to find it as well, figuring their
> >technical people were better at this sort of thing than I am... no
> >luck.
> >
> >I don't think Oracle stores the statement and who issued it, just
> >the
> >rollback info necessary and the fact that there is a lock.
> >
> >
> >--- Alan Davey <adavey_at_competitrack.com> wrote:
> >> Hi All,
> >>
> >> I've noticed some locks on various tables and I'm trying to figure
> >> out which DML statements are causing the locks. In this example,
> >the
> >> lock isn't being released because the developer forgot to include
> >a
> >> commit/rollback.
> >>
> >> If I look at v$session which is causing the lock and query v$sqlarea
> >> with the values in sql_address and prev_sql_addr, I only see select
> >> statements that were issued after the DML (in this case a delete).
> > I
> >> can query
> >> v$sqlarea with the locked table name and find the delete statement,
> >> but how do I link this back to the sid that issued it? Also, what
> >if
> >> there had been multiple DML statements by this user, how would
> >I know
> >> which was the first/last one executed?
> >>
> >> I'm RTFMing, but so far no luck. Any help would be greatly
> >> appreciated.
> >>
> >> Regards,
> >> --
> >>
> >> Alan Davey
> >> adavey_at_competitrack.com
> >> 212-604-0200 x106
> >>

I hate to disagree even partially with Rachel, but IMHO if a lock exists, then a cursor *may* still be open somewhere (I insist on 'may' because this is typically untrue with SQL*Plus). In that case, V$OPEN_CURSOR provides the SID and the hash value/address allowing to join with V$SQL_TEXT. There is, also, V$SQL_CURSORS. Unfortunately, this one is, I think, 'private' to a session and making use of CURNO requires a plunge into the X$ tables. If I may suggest something, it is to create an 'after' trigger on the locked table which systematically logs (in an autonomous transaction) the statement which fired it. If I do not err, when a lock occurs then the last logged statement should be the offending one.

-- 
Regards,

Stephane Faroult
Oriole Software
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.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).