Technology at a glance!

Today I had a full production stop on a system under my command.

A simple delete statement was blocked. I normally fire up TOAD when this occures.
But I found (read: google) a more simple way to do this in plain sql:

SELECT s1.username || '@' || s1.machine ||
' ( SID=' || s1.SID || ' )  is blocking ' ||
s2.username || '@' || s2.machine ||
' ( SID=' || s2.SID || ' ) ' AS blocking_status
FROM v$lock l1, v$session s1, v$lock l2, v$session s2
WHERE s1.SID = l1.SID
AND s2.SID = l2.SID
AND l1.BLOCK = 1
AND l2.request > 0
AND l1.id1 = l2.id1
AND l2.id2 = l2.id2

It produces a nice result:

BLOCKING_STATUS

user@host ( SID=24 )  is blocking user@host( SID=31 )
SYSTEM@host( SID=42 )  is blocking user@host( SID=68 )
SYSTEM@host( SID=42 )  is blocking user@host( SID=24 )
SYSTEM@host( SID=42 )  is blocking user@host( SID=45 )
user@host( SID=45 )  is blocking user@host( SID=43 )
user@host( SID=68 )  is blocking user@host( SID=44 )
It seems like our DBA was generating a HUGE index in daily production hours.

Somthing he usually knife other pure souls for.

Shame on you Mr. DBA, shame on you.
Never the less : Today I knifed him, and what a feeling!

Benjamin Sølberg Oracle