Stephane you are more than welcome to disagree with me. Of course, when
I tried to find the sql, I was on version 7.3 so autonomous
transactions were unavailable.
- Stephane Faroult <sfaroult_at_oriole.com> wrote:
> 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).
Do You Yahoo!?
Yahoo! Finance - Get real-time stock quotes
http://finance.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Rachel Carmichael
INET: wisernet100_at_yahoo.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 Fri Aug 30 2002 - 21:28:26 CDT