Sessions that are blocked or blocking other sessions- ORACLE 10g

Finding Total Session Count:


SELECT 'Currently, ' || (SELECT COUNT(*) FROM V$SESSION) || ' out of ' ||
       VP.VALUE || ' connections are used.' AS USAGE_MESSAGE
  FROM V$PARAMETER VP
 WHERE VP.NAME = 'sessions';


To find sessions that are blocked or blocking other sessions, the one of the following queries:

select t.process, t.sid, t.SERIAL#, t.blocking_session, t.USER#, t.USERNAME

  from v$session t

 where blocking_session is not null;

select l1.sid, ' IS BLOCKING ', l2.sid

  from v$lock l1, v$lock l2

   where l1.block =1 and l2.request > 0

  and l1.id1=l2.id1

  and l1.id2=l2.id2;