Query to find out locking on oracle database

DBA's often have to come across a situation where we have to find the locks on the database. This query provides in much detail and easy way to figure out the locking on the database.

In RAC:

sql> select s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ') is blocking'
        || s2.username || '@' || s2.machine || '( SID=' || s2.sid || ')' from
gv$lock l1, gv$session s1, gv$lock l2, gv$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;

In Standalone:

sql>select s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ') is blocking'
       || s2.username || '@' || s2.machine || '( SID=' || s2.sid || ')' 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;

Query Output:

S1.USERNAME||'@'||S1.MACHINE||'(SID='||S1.SID||')ISBLOCKING'||S2.USERNAME||'@'||
--------------------------------------------------------------------------------
SYS@host1.abc.com ( SID=229) is blockingSYS@host1.abc.com( SID=226)


With Serial#:

SQL>SELECT s1.username || '@' || s1.machine
          || ' ( SID=' || s1.sid ||','||s1.serial# || ' )  is blocking '
          || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
          FROM gv$lock l1, gv$session s1, gv$lock l2, gv$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 l1.id2 = l2.id2;


CTM8USER@host1.abc.com ( SID=10,7373 )  is blocking CTM8USER@host1.abc.com ( SID=5 )
CTM8USER@host1.abc.com ( SID=10,7373 )  is blocking @host1.abc.com ( SID=244 )
CTM8USER@host1.abc.com ( SID=10,7373 )  is blocking CTM8USER@host1.abc.com ( SID=244)