Hi , I am using the following query to find out the idle time of a user session .
,to_char(logon_time, 'mm/dd/yy hh24:mi:ss') logon_time
-- idle time
-- days separately
,substr('0'||trunc(last_call_et/86400),-2,2) || ':' ||
substr('0'||trunc(mod(last_call_et,86400)/3600),-2,2) || ':' ||
substr('0'||trunc(mod(mod(last_call_et,86400),3600)/60),-2,2) || ':' ||
FROM v$session s, v$process p
WHERE s.username IS NOT NULL
-- use outer join to show sniped sessions in
-- v$session that don't have an OS process
AND p.addr(+) = s.paddr
My question is whether my query is right? I am using last_call_et column to find the idle time . also if a session is not executing any sql statement then its last_call_et will be reset to 0 and starts update . right?
12-03-2002, 01:42 AM
Seems alright to me. You may add another condition "where status = 'INACTIVE' "
last_call_et is the number of seconds since the session was last active.
12-03-2002, 01:54 AM
I also found strange behaviour in v$session. i found in of our Database the status show 'ACTIVE' and also idle time is more than 2 days. How come this is possible ? are they not contradictory ?
appreciate any help in advance.
12-03-2002, 02:21 AM
Are you sure you are not looking for any background process?
12-03-2002, 03:19 AM
Yeah.. I was very much surprised to see that . If its ACTIVE then the idle time should be 0 .