Also what do you/we want v$sql to contain if I run UPDATE T1 SET C1 = 'NEW VALUE' WHERE ID = 1; and there are one or more triggers on T1 that modify other tables and hence block people. I'd suggest that v$sql containing the sql_id of the SQL issued by the session is sensible, but that it may not be that SQL that is causing the issues. In general reporting the blocking session *is *enough to identify what is going on, I suspect that where it isn't enough there will usually be one or more confounding factors that make identification of the exact SQL somewhat problematic. On Mon, Jul 2, 2012 at 4:24 PM, David Fitzjarrell <oratune@xxxxxxxxx> wrote: > And V$SESSION also proviees PREV_SQL_ID to report the prior sql statement > from that session. That being said I'm coming up with a NULL value for > SQL_ID from an active blocking session. Apparently SQL_ID isn't updated > when I originally thought it would be. > > David Fitzjarrell > > > > ________________________________ > From: Alex Fatkulin <afatkulin@xxxxxxxxx> > To: oratune@xxxxxxxxx > Cc: "martin.a.berger@xxxxxxxxx" <martin.a.berger@xxxxxxxxx>; " > kamusis@xxxxxxxxx" <kamusis@xxxxxxxxx>; oracle_l <Oracle-L@xxxxxxxxxxxxx> > Sent: Monday, July 2, 2012 7:30 AM > Subject: Re: How to find the exact SQL locking others? > > SQL_ID from V$SESSION shows current SQL_ID for a session, a blocking > session might be on a totally different statement (from the one which > blocked someone else) when you look it up. > > If you're still not convinced, try the above example but flush your > shared pool at the end. > > On Mon, Jul 2, 2012 at 9:21 AM, David Fitzjarrell <oratune@xxxxxxxxx> > wrote: > > V$SESSION_BLOCKERS provides the information to identify the blocking > session as it reports the sid and serial# of blocked sessions as well as > the sid and serial# of the blocking session. This information along with > SQL_ID from V$SESSION and the V$SQLAREA view would, as far as I can tell, > report on the blocking SQL statement. So it appears that Oracle does keep > track of this information (if not directly then indirectly). > > > > David Fitzjarrell > > > > -- > Alex Fatkulin, > http://afatkulin.blogspot.com > > Enkitec, > http://www.enkitec.com > -- > //www.freelists.org/webpage/oracle-l > > > -- Niall Litchfield Oracle DBA http://www.orawin.info -- //www.freelists.org/webpage/oracle-l