Message-ID: <F001.004C4BCD.20020830182826@fatcity.com>
Date: Fri, 30 Aug 2002 18:28:26 -0800
To: Multiple recipients of list ORACLE-L <ORACLE-L@fatcity.com>
Sender: root@fatcity.com
Reply-To: ORACLE-L@fatcity.com
Errors-To: ML-ERRORS@fatcity.com
From: Rachel Carmichael <wisernet100@yahoo.com>
Subject: Re: Table Locks
Organization: Fat City Network Services, San Diego, California
Precedence: bulk
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

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@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@competitrack.com
> > 212-604-0200  x106
> > 
> > On 8/29/2002 10:43 PM, Rachel Carmichael <wisernet100@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@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@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@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@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@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@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).