Killing Sessions in Oracle Database Cont'd

In situations where a session is waiting on a resource or performing operations such as roll back the session will not be killed until such operations are completed. When this happens, the status of the session will be changed to "MARKED FOR KILL" and will be killed when the operation is completed. This can be a little frustrating for the user if the session in question was created from a client application like SQL Navigator because the cusor/control is not returned to the user until the session is eventually terminated.

Use the immediate option of the alter system kill session command to remove the session from the system at once; 

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

Another alternative method is to disconnect the session from the database by killing the dedicated or shared server process from the server. This method also has the immediate option, see sample script below

SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' POST_TRANSACTION;

SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE;

Hope this helps!

Kehinde.