Session Monitoring

Blocking Sessions:

SELECT
   l1.sid || ' is blocking ' || l2.sid blocking_sessions
FROM
   gv$lock l1, gv$lock l2
WHERE
   l1.block = 1 AND
   l2.request > 0 AND
   l1.id1 = l2.id1 AND
   l1.id2 = l2.id2;

SELECT s1.username || '@' || s1.machine
    || ' ( SID=' || s1.sid || ' )  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 l2.id2 = l2.id2 ;

Sessions count:

select resource_name, current_utilization, max_utilization,LIMIT_VALUE,inst_id from gv$resource_limit where resource_name in ('processes','sessions');


Session level Wait events:

col program for a19
col event for a50
set lines 900 pages 900
col machine for a40
select inst_id,sid,program,sql_id,event,machine,state,logon_time,SECONDS_IN_WAIT,last_call_et/60 mins_running from gv$session
where username is not  null and program not like 'oraagent.bin%' and program not like 'emagent%' and program not like 'sqlplus%'
 and program not like 'oracle@%' and program<>'OMS' and event not like 'SQL*Net message%';

Session and SQL Running:

col sid form 9999
col curr form a120 head "     Current SQL"
bre on sid skip 2
set long 10000
select a.sid sid,b.sql_text curr
from gv$session a, gv$sql b
where a.sql_address=b.address
and a.sid=&1
order by 1;