user session idle time
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: user session idle time

  1. #1
    Join Date
    Nov 2000
    Posts
    35

    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?

  2. #2
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    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

  3. #3
    Join Date
    Nov 2000
    Posts
    35
    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.

  4. #4
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Are you sure you are not looking for any background process?

    Sanjay

  5. #5
    Join Date
    Nov 2000
    Posts
    35
    Yeah.. I was very much surprised to see that . If its ACTIVE then the idle time should be 0 .


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width