Re: How to find the exact SQL locking others?

From: Alex Fatkulin <afatkulin_at_gmail.com>
Date: Mon, 2 Jul 2012 09:30:39 -0400
Message-ID: <CAMVw97JefFk-uqXrtanSqjriRY4FQK2S9bOt9gjoJkMOqELk6w_at_mail.gmail.com>

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 - 08:30:39 CDT