Kill The Inactive Session After 2 Hours
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Kill The Inactive Session After 2 Hours

  1. #1
    Join Date
    Apr 2002
    Posts
    86

    Kill The Inactive Session After 2 Hours

    Can some body help in wrting the script for this to
    KILL THE INACTIVE SESSION AFTER 2 HOURS ( if it is has inactive from last 2 hours or more.)

    Thanks In Advance

  2. #2
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    Why don't you use profile (IDLE_TIME) ?

  3. #3
    Join Date
    Apr 2002
    Posts
    86
    Thanks.
    Can you give me an example.

  4. #4
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Try this

    SET RESOURCE_LIMIT=TRUE in your init.ora file

    bounce ur DB

    then

    CREATE PROFILE IDLETEST LIMIT IDLE_TIME 120;

    alter user TESTUSER profile IDLETEST;

    HTH
    Amar
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  5. #5
    Join Date
    Sep 2001
    Location
    Düsseldorf, Germany.
    Posts
    588
    As adewri wrote, you need to create profile and assign it to the user.

    CREATE PROFILE IDLETEST LIMIT IDLE_TIME n; where n is time specified in minutes.

    IDLE_TIME: Specify the permitted periods of continuous inactive time during a session.

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    However setting profile with the IDLE_TIME will not actually kill the session after the specified time of inactivity! That session will stay there in inactive state even after the specified idle time. It will only be disconnected when next activity on that session is attempted. So if user doesn't get back and try to do something after two hours of inactivity, the session will still be hanging there on the database. It will still eating the resources, prossibly forewer if you don't kill it manually!
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  7. #7
    Join Date
    Jul 2002
    Location
    California
    Posts
    128
    Use the v$session view to find the current sessions.

    The LOGON_TIME field will give the logon time of a session.

    If SYSDATE - LOGON_TIME is greater than 2 hours, then you found a session that has been active longer than two(2) hours.

    Kill the session manually as jmodic stated
    ALTER SYSTEM KILL SESSION 'sid,serial#'

    P.S. Make sure you do NOT kill the oracle internal processes. If you do, you will crash the DB.
    alapps

    Fast, Cheap, Reliable... Pick Two(2)

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    you cannot use SYSDATE - LOGON_TIME to determine if a session can be killed or not

  9. #9
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Of course, LOGON_TIME doesn't tell you anything about how long the sesson has been inactive. For that you have to check column LAST_CALL_ET, which stores the elapsed time in seconds from the last call to the RDBMS by that session.

    To find all the sessions that have been inactive for more than 2 hours, your query would look something like:
    Code:
    SELECT * FROM v$session
    WHERE STATUS != 'ACTIVE'
    AND last_call_et > 2*60*60;
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

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