Session Management - Oracle 10g/11g Data and Database Management Utilities - page 164

On a three tiered architecture the problem is even worse. If the application tier
doesn't provide a mechanism to detect and eliminate hung sessions, then in case
of a connection failure between the application tier and the client tier, the database
session may remain active and the locks might be held. If the user tries reconnecting
it may attempt the same transactions against the same locked rows its last session left
behind. This session will end up hung and the DBA will have to manually kill the
blocking session.
Killing sessions
Enterprise Manager shows two options to get rid of a session, you must be aware
of the behavior and the implications of the different options. When killing a session
from enterprise manager, there are two options: KILL IMMEDIATE and POST
TRANSACTION . On the first case the command issued is:
ALTER SYSTEM KILL SESSION 'SID,SERIAL#' IMMEDIATE;
And for the second option, the command issued is:
ALTER SYSTEM DISCONNECT SESSION 'SID,SERIAL#' POST_TRANSACTION;
When killing sessions in character mode there are more options:
ALTER SYSTEM KILL SESSION 'SID,SERIAL#' [IMMEDIATE];
KILL SESSION : This clause with no arguments instructs Oracle to terminate a
session, rolls back any ongoing transactions and release locks, and it can partially
recovery session resources. This marks the session status as KILLED, and this status
remains until the user process issues any SQL command against the database. When
this happens, the RDBMS replies with the ORA-00028: your session has been killed
message and the session is definitely wiped off. If the session shows no further
database activity, then the session may remain in KILLED status in the V$SESSION
indefinitely until the client interacts with the database again.
IMMEDIATE : This option will mark the session as KILLED in the V$SESSION
view. The difference between a regular KILL SESSION and an IMMEDIATE KILL
SESSION is that this one will also leave the KILLED status for a while (about 30
seconds) then Oracle will definitely get rid of it. This option releases all session
allocated resources. It rolls back pending transactions, and it will return control
immediately. When a session is killed with the immediate option, it receives the
ORA-03113: end-of-file on communication channel error message.
ALTER SYSTEM DISCONNECT SESSION 'SID,SERIAL#' [POST_TRANSACTION |
IMMEDIATE];