Check the last activity time and how long the session in Idle state in Oracle

Calculate the session how long it will be in idle State

Check the last activity time and how long the session in Idle state:

column SID format 999
column lastactivitytime format a25
column IdletimeinHrs format 9999999.99
select sid, to_char((sysdate - (hsecs - value)/(100606024)), 'dd-mon-yy hh:mi:ss') "Lastactivitytime" (hsecs - value)/(10060*60) "IdletimeinHrs"
from v$timer, v$sesstat
where statistic# = (select statistic# from v$statname
where name = 'process last non-idle time');

Query will be used to calculate the session idle time:

select st1.sid "SID", s.schemaname "User",
((sysdate - s.logon_time)*24*60*60) - (st1.value - st2.value) "Idle Time"
from
v$session s,
v$sesstat st1,
v$sesstat st2
where
s.sid = st1.sid and
s.sid = st2.sid and
st1.statistic# = (select statistic# from v$statname
where name = 'process last non-idle time') and
st2.statistic# = (select statistic# from v$statname
where name = 'session connect time');

Check the user last call or last activity after idle time:

LAST_CALL_ET that contains the number of seconds since the last call was made.

Select LAST_CALL_ET/60 "LastcallinMINS" from V$SESSION;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.