user session idle time

Hi , I am using the following query to find out the idle time of a user session .

SELECT
s.sid,s.serial#,s.username
,s.status
,substr(s.machine,1,10)
,s.osuser,s.module
,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) || ':' ||
-- hours
substr('0'||trunc(mod(last_call_et,86400)/3600),-2,2) || ':' ||
-- minutes
substr('0'||trunc(mod(mod(last_call_et,86400),3600)/60),-2,2) || ':' ||
--seconds
substr('0'||mod(mod(mod(last_call_et,86400),3600),60),-2,2) idle_time
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
ORDER BY
idle_time desc;

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?

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.

Sanjay

thanks sanjay,

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.

Are you sure you are not looking for any background process?

Sanjay

Yeah.. I was very much surprised to see that . If its ACTIVE then the idle time should be 0 .

:confused: