Killed sessions remain in DB

[efrijters]

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?

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

[stmontgo]

ummmmm..why are you killing sys sessions?

[jovery]

It's possible your OS sessions relating to the killed sessions are still there, kill these and the sessions should disappear.

Regards

[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?

[efrijters]

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...

[jovery]

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

[jodie]

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