Re: How to find the exact SQL locking others?

You don't need triggers for that...

if session issues 1:

update t1 set c1=y where id =x <= this will later block people
update t2 set ....
update t3 set ....
update t4 set....

session 2:

update t1 set c1=z where id=x <= this gets blocked on the very first
statement issued by session 1

sql_id/prev_sql_id for session 1 will be useless. The first statement
executed by session 1 might event be out of shared pool by now!

On Mon, Jul 2, 2012 at 11:45 AM, Niall Litchfield
<niall.litchfield@xxxxxxxxx> wrote:
> 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.
>

-- 
Alex Fatkulin,
http://afatkulin.blogspot.com

Enkitec,
http://www.enkitec.com
--
http://www.freelists.org/webpage/oracle-l

Other related posts: