Category Archives: Oracle

In this we are handling Oracle Database Administration and development task. If provide solution of ORA Errors and configuration Steps for setup in Oracle.

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;