Oracle session connection status...

http://anuj-singh.blogspot.com/2011/07/oracle-top-sql-spent-more-on-cpuwaitio.html

--For Rac

col NAME for a20
select
distinct 
inst_id,
decode(nvl(to_char(s.sid),-1),-1,'DISCONNECTED','CONNECTED') "STATUS",
topsession.session_id                                        "SESSION_ID",
u.name                                                       "NAME",
topsession.program                                           "PROGRAM",
max(topsession.CPU)                                          "CPU",
max(topsession.WAITING)                                      "WAITING",
max(topsession.IO)                                           "IO",
max(topsession.TOTAL)                                        "TOTAL"
from ( select
ash.inst_id,
ash.session_id,
ash.session_serial#,
ash.user_id,
ash.program,
sum(decode(ash.session_state,'ON CPU',1,0)) "CPU",
sum(decode(ash.session_state,'WAITING',1,0)) - sum(decode(ash.session_state,'WAITING',
decode(en.wait_class,'User I/O',1, 0 ), 0)) "WAITING" ,
sum(decode(ash.session_state,'WAITING',
decode(en.wait_class,'User I/O',1, 0 ), 0)) "IO" ,
sum(decode(session_state,'ON CPU',1,1)) "TOTAL"
from gv$active_session_history ash, v$event_name en
where en.event# = ash.event#
group by inst_id,session_id,user_id,session_serial#,program
order by sum(decode(session_state,'ON CPU',1,1)) ) topsession, v$session s, user$ u
where u.user# =topsession.user_id
/* outer join to v$session because the session might be disconnected */
and topsession.session_id = s.sid (+)
and topsession.session_serial# = s.serial# (+)
and u.name not in ('SYS','SYSTEM') ---<<<<<<<<<<<<<<<<<<<<<--------
and status!='DISCONNECTED'
group by inst_id,topsession.session_id, topsession.session_serial#, topsession.user_id, topsession.program, s.username,s.sid,s.paddr,u.name
order by max(topsession.TOTAL) desc

-- =====================================

--for Single Instance 


col NAME    for a20
col STATUS  for a15
col PROGRAM for a50
select distinct 
decode(nvl(to_char(s.sid),-1),-1,'DISCONNECTED','CONNECTED') "STATUS",
topsession.session_id                                        "SESSION_ID",
u.name                                                       "NAME",
topsession.program                                           "PROGRAM",
max(topsession.CPU)                                          "CPU",
max(topsession.WAITING)                                      "WAITING",
max(topsession.IO)                                           "IO",
max(topsession.TOTAL)                                        "TOTAL"
from ( 
select
ash.session_id,
ash.session_serial#,
ash.user_id,
ash.program,
sum(decode(ash.session_state,'ON CPU',1,0)) "CPU",
sum(decode(ash.session_state,'WAITING',1,0)) - sum(decode(ash.session_state,'WAITING',
decode(en.wait_class,'User I/O',1, 0 ), 0)) "WAITING" ,
sum(decode(ash.session_state,'WAITING',
decode(en.wait_class,'User I/O',1, 0 ), 0)) "IO" ,
sum(decode(session_state,'ON CPU',1,1)) "TOTAL"
from v$active_session_history ash, v$event_name en
where en.event# = ash.event#
group by session_id,user_id,session_serial#,program
order by sum(decode(session_state,'ON CPU',1,1)) ) topsession, v$session s, user$ u
where u.user# =topsession.user_id and
/* outer join to v$session because the session might be disconnected */
topsession.session_id = s.sid (+) and
topsession.session_serial# = s.serial# (+)
and u.name not in ('SYS','SYSTEM')          ---<<<<<<<<<<<<<<<<<<<<<--------
and status!='DISCONNECTED' ---<<<<<<<<<<<<<<<<<<<<<--------
group by topsession.session_id, topsession.session_serial#, topsession.user_id, topsession.program, s.username,s.sid,s.paddr,u.name
order by max(topsession.TOTAL) desc

set linesize 300
col KILL for a18
col NAME for a20
col PROGRAM for a50
select
distinct 
--inst_id,
'''' || topsession.SESSION_ID || ',' || topsession.session_serial# ||',@'|| topsession.inst_id||'''' kill,
decode(nvl(to_char(s.sid),-1),-1,'DISCONNECTED','CONNECTED') "STATUS",
topsession.session_id                                        "SESSION_ID",
u.name                                                       "NAME",
topsession.program                                           "PROGRAM",
max(topsession.CPU)                                          "CPU",
max(topsession.WAITING)                                      "WAITING",
max(topsession.IO)                                           "IO",
max(topsession.TOTAL)                                        "TOTAL"
from ( select
ash.inst_id,
ash.session_id,
ash.session_serial#,
ash.user_id,
ash.program,
sum(decode(ash.session_state,'ON CPU',1,0)) "CPU",
sum(decode(ash.session_state,'WAITING',1,0)) - sum(decode(ash.session_state,'WAITING',decode(en.wait_class,'User I/O',1, 0 ), 0)) "WAITING" ,
sum(decode(ash.session_state,'WAITING',decode(en.wait_class,'User I/O',1, 0 ), 0)) "IO" ,sum(decode(session_state,'ON CPU',1,1)) "TOTAL"
from gv$active_session_history ash, v$event_name en
where en.event# = ash.event#
group by ash.inst_id,session_id,user_id,session_serial#,program
order by sum(decode(session_state,'ON CPU',1,1)) ) topsession, v$session s, user$ u
where u.user# =topsession.user_id
/* outer join to v$session because the session might be disconnected */
and topsession.session_id = s.sid (+)
and topsession.session_serial# = s.serial# (+)
and u.name not in ('SYS','SYSTEM') ---<<<<<<<<<<<<<<<<<<<<<--------
and status!='DISCONNECTED'
--group by inst_id,topsession.session_id, topsession.session_serial#, topsession.user_id, topsession.program, s.username,s.sid,s.paddr,u.name
group by '''' || topsession.SESSION_ID || ',' || topsession.session_serial# ||',@'|| topsession.inst_id||'''',topsession.session_id, topsession.session_serial#, topsession.user_id, topsession.program, s.username,s.sid,s.paddr,u.name
order by max(topsession.TOTAL) desc