Last Activity moment of session
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Last Activity moment of session

  1. #1
    Join Date
    Oct 2002
    Location
    Breda, The Netherlands
    Posts
    317

    Last Activity moment of session

    Hello,

    I'm looking for a column in a v$-view to determine the last activity moment for a session. I want to locate "sleeping" session and optionally kill them.

    In DBA Studio an item "Last Activity on [date]" is visible when you double click a session. That's the value I'm looking for.

    Can you guys help me?

    Thanks in advance.


    This is what I have sofar:
    Code:
    select username,sid,serial#,logon_time,status 
      from v$session
      where status<>'ACTIVE' and logon_time < trunc(sysdate)
      order by logon_time
    An expert is one who knows more and more about less and less until he knows absolutely everything about nothing.

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    v$session.last_call_et

  3. #3
    Join Date
    Oct 2002
    Location
    Breda, The Netherlands
    Posts
    317
    v$session.last_call_et results in a number,not a datetime-type.

    I found this via google:
    "I've used it (last_call_et) in the past to measure how long sessions have been inactive, but looks like it will show the elapsed time since the last query (last call) began as well. "
    Last edited by efrijters; 11-14-2003 at 06:19 AM.
    An expert is one who knows more and more about less and less until he knows absolutely everything about nothing.

  4. #4
    Join Date
    Oct 2002
    Location
    Breda, The Netherlands
    Posts
    317
    Okay, this does the trick:

    Code:
    select
        sid,username,status,
        to_char(logon_time,'dd-mm-yy hh:mi:ss') "LOGON",
        floor(last_call_et/3600)||':'||
        floor(mod(last_call_et,3600)/60)||':'||
        mod(mod(last_call_et,3600),60) "IDLE",
        program
      from v$session
      where type='USER'
      order by last_call_et
    LAST_CALL_ET is the passed time since the last call and can be converted to time.

    Problem solved...
    An expert is one who knows more and more about less and less until he knows absolutely everything about nothing.

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