SQL Session Info

Contents of this Post

1. Count Current session
2. Session Info

Count Current session
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';

Session Info
set linesize 200 pages 1000
set head on
set feedback on
col sid head "Sid" form 9999 trunc
col serial# form 99999 trunc head "Ser#"
col username form a10 trunc
col osuser form a9 trunc
col machine form a20 trunc head "Client|Machine"
col program form a35 trunc head "Client|Program|Module"
col login form a11
col "last call"  form 9999999 trunc head "Last Call|In Min"
col status form a6 trunc
col KILL_SESSION_SCRIPT for a50
col "Last Activity" for a13
col "Idle_Time" for 99,999.00

select sid,serial#,substr(username,1,10) username,substr(osuser,1,10) osuser,substr(program||'|'||module,1,25) program
,substr(machine,1,22) machine, to_char(logon_time,'ddMon hh24:mi') login, PROCESS,    -- Operating system client system process ID
to_char(sysdate-last_call_et/86400,'ddMon hh24:mi') "Last Activity"
,round(((sysdate-last_call_et/86400)-logon_time),2)*24 "Idle_Time",last_call_et/60 "last call",status
--,'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' IMMEDIATE;' Kill_Session_Script
from v$session
Where TYPE <> 'BACKGROUND'
--And status='INACTIVE' --'KILLED'
--And to_number(round(((sysdate-last_call_et/86400)-logon_time),2)*24) > 10    -- Session Idle from lsat n Hrs
--AND upper(USERNAME) like '&USERNAME%'
--And SID=116
--And logon_time < sysdate-1 -- < sysdate-(1/24)
--And program = 'ArcSOC.exe'
--Order by logon_time, "Last Activity", "Idle_Time", status, login
order by "Idle_Time" asc;