Re: How to find the exact SQL locking others?

From: David Fitzjarrell <oratune_at_yahoo.com>
Date: Mon, 2 Jul 2012 08:24:19 -0700 (PDT)
Message-ID: <1341242659.75800.YahooMailNeo_at_web160904.mail.bf1.yahoo.com>

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_at_gmail.com> To: oratune_at_yahoo.com
Cc: "martin.a.berger_at_gmail.com" <martin.a.berger_at_gmail.com>; "kamusis_at_gmail.com" <kamusis_at_gmail.com>; oracle_l <Oracle-L_at_freelists.org> 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_at_yahoo.com> 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
--
http://www.freelists.org/webpage/oracle-l

Received on Mon Jul 02 2012 - 10:24:19 CDT