What can stop ALTER SYSTEM KILL SESSION 'n,n' from actually killing a session? A particular example: A multi-threaded application runs on a separate server against an Oracle 8.1.7 database. The server running the application restarts. When running a query for the current database sessions using v_$session the now dead application's sessions still appear. Issuing a KILL seems to make no difference.
The sessions eventually disappear from v_$session but I am unsure as to whether the DB has tidied these up because it realises they are inactive or because the KILL has marked them as inactive and the DB gets around to tidying them up. In any case why would the database not kill the session immediately as it does other user sessions? Or, is the database killing the sessions immediately and v_$session just isn't the right place to look?
I would appreciate any help.
Solution: Life after ALTER SYSTEM KILL SESSION
I found this very true too.
Alter system kill session works mostly for sessions from sqlplus but not for external application such as JDBC, ODBC apps.
And I checked this with Oracle metalink, here is what they told me: It is true that alter system kill session does not actually kill them. What is always said is server need to rollback all uncommited changes and as it is done by PMON in backgraund it is much slowlier than done by user process itself when explicit rollback statement issued.
If an active session cannot be interrupted (for example, it is performing network I/O or rolling back a transaction), the session cannot be terminated until the operation completes. In this case, the session holds all resources until it is terminated. Additionally, the session that issues the ALTER SYSTEM statement to terminate a session waits up to 60 seconds for the session to be terminated; if the operation that cannot be interrupted continues past one minute, the issuer of the ALTER SYSTEM statement receives a message indicating that the session has been "marked" to be terminated. A session marked to be terminated is indicated in V$SESSION with a status of "KILLED" and a server that is something other than "PSEUDO."
If the transaction cannot be rolled back by pmon, you may find errors or something indicating what the problem was in the pmon background trace file.