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