What happens when a session is killed?

When a session is killed by an ALTER SYSTEM KILL SESSION 'nnnn,nnnn', it won’t necessarily vanish immediately. If the session has made changes to the database, they have to be undone just as if you had coded a ROLLBACK. Drastic action, such as a forced reboot of the database, may make the killed session vanish, but the rollback still has to be done.

Generally it is best to leave the client program, such as TOAD or SQL*Plus, running until the rollback is complete. That way, the client will receive the ‘Your session has been killed’ error and the database session can exit cleanly.

You can monitor how much work a session has still to rollback using an SQL like this :

SELECT
   vt.used_ublk ,
   vs.sid,
   vs.serial#,
   vs.username,
   vs.status,
   vs.schemaname,
   vs.osuser,
   vs.machine,
   vs.terminal,
   vs.program,
   vs.prev_hash_value,
   vs.sql_hash_value,
   vt.start_ubablk,
   used_urec
FROM v$session vs, v$transaction vt, v$sqlarea a
WHERE vs.taddr = vt.addr
AND bitand(vt.flag,POWER(2,7))> 0
AND a.hash_value(+) =
   DECODE(vs.sql_hash_value,
           0,vs.prev_hash_value,
           vs.sql_hash_value)

The USED_UREC should steadily decrease until the rollback is complete.