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 <[EMAIL PROTECTED]> 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 > > [EMAIL PROTECTED] > > 212-604-0200 x106 > > > > On 8/29/2002 10:43 PM, Rachel Carmichael <[EMAIL PROTECTED]> > 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 <[EMAIL PROTECTED]> 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 > > >> [EMAIL PROTECTED] > > >> 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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
