Posted by Most people are aware of the STATUS column in V$SESSION. If it’s ‘ACTIVE’ then that connection is in the process of consuming database resources (running a SQL statement etc).
However, a lesser known column which is probably even more useful is the LAST_CALL_ET column. The name suggests the time since the last call, but the documentation provides a more useful description:
If the session STATUS is currently ACTIVE, then the value represents the elapsed time (in seconds) since the session has become active.
If the session STATUS is currently INACTIVE, then the value represents the elapsed time (in seconds) since the session has become inactive.
Thus this is a great column to get a view on what sessions are doing on your system, for example
select s.sid||','||s.serial# sess,
s.USERNAME,
s.last_call_et,
s.status,
s.sql_address,
s.program
from v$session s
where ( s.status = 'ACTIVE' and s.last_call_et > 10 ) or -- has been active for 10 seconds or more
( s.status != 'ACTIVE' and s.last_call_et > 1200 ); -- has been inactive for 20 mins or more
Learning is not a spectator sport 
Hi! I have interesting select that allows to find nonsystem long active sessions on all nodes of cluster with command to kill them. Hope it can be useful.
select t2.sql_text, sess.inst_id, sess.username, sess.sid, sess.serial#, sess.last_call_et, sess.wait_class, sess.event, sess.machine, sess.program, sess.sql_id, sess.kill_command from
(select t1.inst_id, t1.username, t1.sid, t1.serial#, t1.last_call_et, t1.wait_class, t1.event, t1.machine, t1.program, t1.sql_id, ‘alter system kill session ”’ ||to_char(t1.SID)||’,’||to_char(t1.SERIAL#)||’,@’||to_char(t1.inst_id)||”’ immediate;’ kill_command
from gv$session t1
where t1.status=’ACTIVE’ and t1.last_call_et>300 and username is not null) sess,
gv$sql t2
where sess.SQL_ID=t2.SQL_ID and sess.inst_id=t2.inst_id
group by t2.sql_text, sess.inst_id, sess.username, sess.sid, sess.serial#, sess.last_call_et, sess.wait_class, sess.event, sess.machine, sess.program, sess.sql_id, sess.kill_command
Hi Oleg,
Thanks for the contribution.
Excellent, thanks for this. It’s going into my stash!
One minor comment:
with SQL+ (yes, I *still* use it) the semi-colon followed by the “–” is a no-no.
Nice catch, I added the comments in as an afterthought on the post. Here’s my complete version of a basic (single instance) session viewer SQL, where last_call_et is just shown in brackets for each session
select s.sid, s.serial#,
s.username
|| ‘ (‘||s.last_call_et||’)’ username,
case when s.lockwait is null then
case when s.username is null then
nvl2(j.job_sid,’ACTIVE’,’INACTIVE’)
else
s.status
end
else ‘BLOCKED’
end status,
s.sql_id sql_id,
case when s.program is not null then
( case when lower(s.program) like ‘oracle%(%)%’ then regexp_substr(lower(s.program),’^oracle.*\((.*)\).*$’,1, 1, ‘i’, 1)
else s.program
end )
when s.username is null then ( select p.program
from v$process p
where s.PADDR = p.ADDR )
end || ‘-‘ || s.osuser program ,
blocking_session,
case
when blocking_session is null then cast(null as varchar2(1))
else
cast(( select substr(s1.osuser||’-‘||s1.program,1,60)
from v$session s1
where s1.sid = s.blocking_session
) as varchar2(60))
end blocker
from v$session s,
( select sid job_sid
from v$lock
where type = ‘JQ’ ) j
where s.sid = j.job_sid(+)
order by s.sid;
Cheers,
Connor
My FAVORITIST view 🙂
Thank you Connor ! excellent.