Killed sessions remain in DB
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Killed sessions remain in DB

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

    Unhappy Killed sessions remain in DB

    Hello guys,

    I executed the statement below and I saw that a lot of killed sessions remain in the db. How can I release those connections?

    Code:
    select username,sid,serial#,logon_time,status 
    from v$session
    where status<>'ACTIVE' and logon_time < trunc(sysdate)
    order by logon_time
    
    USERNAME                           SID    SERIAL# LOGON_TI STATUS
    --------------------------- ---------- ---------- -------- --------
    SYS                                 13       1412 25-08-03 KILLED
    SYS                                 39      57241 27-08-03 KILLED
    SYSTEM                              52       5396 02-09-03 KILLED
    SYS                                 38      64062 23-09-03 KILLED
    SYS                                 93      33838 23-09-03 KILLED
    SYS                                  8      60551 23-09-03 KILLED
    SYS                                103       1440 23-09-03 KILLED
    SYS                                 73       9639 23-09-03 KILLED
    SYS                                 88      63663 24-09-03 KILLED
    SYS                                 45      27293 24-09-03 KILLED
    Note: We always log off at the end of the day.

    The connectsions were killed by " alter system kill session 'SID, SERIAL#';" and I thought, that after a while they would vanish by the work of pmon...

    SQLNET.ORA contains:
    sqlnet.expire_time = 10


    Help is very appreciated.

    Erik
    Last edited by efrijters; 10-02-2003 at 10:29 AM.
    An expert is one who knows more and more about less and less until he knows absolutely everything about nothing.

  2. #2
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    ummmmm..why are you killing sys sessions?
    I'm stmontgo and I approve of this message

  3. #3
    Join Date
    Jan 2002
    Location
    Up s**t creek
    Posts
    1,525
    It's possible your OS sessions relating to the killed sessions are still there, kill these and the sessions should disappear.

    Regards
    Jim
    Oracle Certified Professional
    "Build your reputation by helping other people build theirs."

    "Sarcasm may be the lowest form of wit but its still funny"

    Click HERE to vist my website!

  4. #4
    Join Date
    Oct 2002
    Location
    Breda, The Netherlands
    Posts
    317
    Lately we had some huge problems with the clients network. Terminal Services crashed sometimes, ISA servers crashed, cpu-fans burned out, virus attacks were encountered. It was a real party, last few weeks...

    Any idea why PMON doesn't do his clean up?
    An expert is one who knows more and more about less and less until he knows absolutely everything about nothing.

  5. #5
    Join Date
    Oct 2002
    Location
    Breda, The Netherlands
    Posts
    317
    Originally posted by jovery
    It's possible your OS sessions relating to the killed sessions are still there, kill these and the sessions should disappear.

    Regards
    hmmm, in that case the service desk can see those connections. I'll call them right away.
    (most of the time I make connections via Terminal Services over a VPN connection. If those fail, the connection stays and the service desk clears them for me.)

    Be right back...
    An expert is one who knows more and more about less and less until he knows absolutely everything about nothing.

  6. #6
    Join Date
    Jan 2002
    Location
    Up s**t creek
    Posts
    1,525
    Originally posted by efrijters
    Lately we had some huge problems with the clients network. Terminal Services crashed sometimes, ISA servers crashed, cpu-fans burned out, virus attacks were encountered. It was a real party, last few weeks...

    Any idea why PMON doesn't do his clean up?
    PMON will not delete the session object until the connected OS session realises it's been killed, once an Oracle session is killed the session is marked and PMON starts it's cleanup of resources.

    The Oracle session remains until the client attempts another request at which point it gets the killed notification and then PMON can remove the session from v$session.

    Therefore is the client session never makes that additional call (i.e it has hung or is defunct) the session will remain.

    HTH
    Jim
    Oracle Certified Professional
    "Build your reputation by helping other people build theirs."

    "Sarcasm may be the lowest form of wit but its still funny"

    Click HERE to vist my website!

  7. #7
    Join Date
    Sep 2001
    Location
    Ohio
    Posts
    334
    I agree with jovery.

    At my last job we used Terminal Servers and had this problem all the time. Users would CTRL-ALT-DEL their session, and it would never go away. We would kill them, but they still stuck around. Like jovery said, the os doesn't realize it's gone so pmon won't pick it up. Dead-connection detection won't work because it looks for clients *machines* that it can't reach.. and the Terminal Server is still there usually.

    The way we got rid of them is to bounce the DB. Until we got time to bounce the DB, we just ignored them.

    Good Luck!
    Jodie

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