Session idle time
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Session idle time

  1. #1
    Join Date
    Oct 2000
    Posts
    25
    Oracle 816, Win2000
    Profile has idle time set to 1 min.
    Why is it that the sessions don't disconnect after the idle time is passed.
    The status of some sessions are INACTIVE and some are SNIPED.
    What does SNIPED mean and how do I get around this issue of disconecting or killing the sessions.
    Sessions do not get disconnected hence max sessions exceeded.
    Any suggestions .........
    Thanks

  2. #2
    Join Date
    Feb 2001
    Location
    Bombay,India
    Posts
    530
    Hi,
    Check whether ur RESOURCE_LIMIT init.ora parameter is set to TRUE.
    U can check it at the Servermanager prompt by typing
    SVRMGR> show parameter resource

    If u have resource_limit set to FALSE,then Oracle will not use the resource management used in the PROFILE.Password Management in profiles doesnot depend on RESOURCE_LIMIT parameter.
    Change the init.ora file and make RESOURCE_LIMIT=TRUE
    and bounce back the database.If u cannot shutdown the database enter the following command
    SVRMGRL> alter system
    set RESOURCE_LIMIT=TRUE;


    Next time when ur database is shutdown and restarted RESOURCE_LIMIT will become FALSE.So better change the init.ora file and bounce the database.

    In case of any help please be free to ask me at rohitsn@altavista.com

    Regards,
    Rohit Nirkhe,Oracle DBA,OCP 8i
    rohitsn@altavista.com


  3. #3
    Join Date
    Oct 2000
    Posts
    25
    Have already done that bit.

  4. #4
    Join Date
    Oct 2000
    Posts
    25
    Please can someone tell me what does SNIPED mean ablut the status of connected user.
    And why is the session not disconnecting after the 1 minute idle time.
    Thanks

  5. #5
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Here is a thread that would help you a bit.

    http://www.dbasupport.com/forums/sho...?threadid=3198


    Oracle wouldn't allow the sessions user who had been marked sniped to reconnect. IF he/she were to try using the same session, it would say session expired. It would physically remove that connection only when the user gets to log out. That is my undestanding of it.

    Sam

    [Edited by sambavan on 04-27-2001 at 10:53 AM]
    Thanx
    Sam



    Life is a journey, not a destination!


  6. #6
    Join Date
    Oct 2000
    Posts
    25
    This is a mobile phone manufacturing plant.
    Systems test phones and results logged into DB.
    Now, everytime a phone is tested, the s/w opens a session to the DB and logs results to DB, not disconnecting it after its finished instead opens another session for the next phone tested results.
    Results are generated ones every 2minutes and there are 10 systems(stations) testing now.
    I have max sessions set to 1048 hoping it would be alright when there will be 100 stations running. This doesnt seem to be the case.

    Now my question is if the session is marked as SNIPED does oracle count that particular session as 1+ to the max sessions or is it that the session is Killed hence not counted.

    Please respond.....

  7. #7
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    It looks like the sniped sessions are not cleaned up immediately.

    Here is a metalink not that has the script that you could run to clean the sniped sessions.

    Problem Description
    -------------------

    If "init.ora" resource_limit = true, and idle_time set in profile.

    When idle_time exceeds, the session status becomes 'sniped' in v$session,
    but sniped sessions never get cleaned up.

    Using 'alter system kill session' to kill the session, session status becomes
    'killed' in v$session, but still is never cleaned up.


    Solution Description
    --------------------

    On Unix, and if using a dedicated server, use the following shell script to
    kill the shadow process (script has been tested on Solaris, AIX, Tru64 and
    HPUX):

    #!/bin/sh
    tmpfile=/tmp/tmp.$$
    sqlplus system/manager < spool $tmpfile
    select p.spid from v\$process p,v\$session s
    where s.paddr=p.addr
    and s.status='SNIPED';
    EOF
    for x in `cat $tmpfile | grep "^[0123456789]"`
    do
    kill -9 $x
    done
    rm $tmpfile


    Explanation
    -----------

    According to several bugs as shown in references, it's still expected behaviour
    that sniped session may never clean up. The only workaround is to kill the
    shadow process


    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  8. #8
    Join Date
    Oct 2000
    Posts
    25
    I have win2000 but i'll find a way to kill the SNIPED sessions.
    Does this mean that SNIPED sessions are counted towards the max sessions, if so then the Profile IDLE time concept of Oracle is of no good.
    I believe there is an explanation to this.

    Thanks any way....

  9. #9
    Join Date
    Oct 2000
    Posts
    25
    I've just noticed that SNIPED do disappear in a while, dont know how long they take though.

  10. #10
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    On that case it could be alive until the PMON gets to awake up and clean the stray session processes.


    For further details on this issue, check the metalink.


    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


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