Finding the Blocking Session at Database

The below query can be used to find the blocking sessions at the database level.

Query

SELECT (SELECT username
          FROM v$session
         WHERE sid = a.sid)
          blocker,
       a.sid,
       ' is blocking ' "IS BLOCKING",
       (SELECT username
          FROM v$session
         WHERE sid = b.sid)
          blockee,
       b.sid,
       a.TYPE "WITH LOCK TYPE"
  FROM v$lock a, v$lock b
 WHERE a.block = 1 AND b.request > 0 AND a.id1 = b.id1 AND a.id2 = b.id2

 

Thanks 

Srini