Disconnect vs Kill
Could someone explain the difference between 'alter session kill ...' vs 'alter session disconnect ...'?
I had a database where connection from the app server exceed process limits. I found that many, many of the connection has been idle for over 200 hours.
So I decided to first 'kill' the sessions. I could then see in v$session that these sessions have a 'Killed', but they linger in the v$session. But also, I see all the O/S processes still there.
Then I decided to use 'disconnect'. This seemed to be really effective ... removing the session from v$session AND also removeing all the O/S process.
So I guess my reason question is ... why use 'kill' at all?
kill removes al locks and stops any running processes, the session remains because it is still alive, it is a graceful way for the client to get a nice message
Do killed session eventially get "cleanup"? I mean ... long after the session is killed will the session stay in the v$session view? But more importantly, how long after will the O/S processes stay. I'm assuming as long as the O/S processes are there (when I do a ps -ef), then Oracle is registering that many current process, which mean it could still be affecting the process limit parameter. if that right?
killed sessions remain as long as the rollback is incomplete.
There are three kinds of lies: Lies, damned lies, and benchmarks...
Unix is user friendly. It's just very particular about who it's friends are.
In a perfect world after rollback is complete your session would get cleanup but in our imperfect world you might find that one or both of your Oracle Session and O/S Process appears to stay there forever.
Originally Posted by dbbyleo
One of the possible causes for this behavior works like this... Client got abort, DBA kills Oracle session, Oracle session remains in "KILLED" status -or is gone after a while, O/S process still out there -alive and kicking.
Usually you can solve this issue by tracing back the offending O/S Process -use PID and kill -9 PID them. In normal circumstances that would take care of both your Oracle session and your O/S process.
Pablo (Paul) Berzukov
Author of Understanding Database Administration
available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
I don't believe it always works, meaning 'disconnect' doesn't always remove the session from v$session. 'disconnect' just closes the connection the session is using; the v$session entry will not go away unless the rollback is complete or the session is killed on OS level.
Originally Posted by dbbyleo
Click Here to Expand Forum to Full Width