How to Kill session in Oracle

The command 'alter system kill session' is not actually killing the target session (like kill -9 would do for OS processes). It just sets a bit in the target sessions state object, which marks that the target session should end. But its entirely up the target session to check this bit and act on it! Normally the target sessions are nice and check that bit often enough in their code, act on it and die. But sometimes when the target session happens to be busy looping in some tight loop (due a bug perhaps) or is hung, then it never gets to check that “please die” bit and never exits. This is why DBAs often need to kill the OS process or thread via OS tools to get rid of that session (and its locks, transactions) as when you kill the OS process, PMON will detect it (if not fast enough then it can be woken up via ORADEBUG WAKEUP call few times) and clean up after that session.

Steps to kill session in Oracle:
1. Use 'alter system kill session' to kill session.
2. If that doesn't work immediately then check whether the target session has acknowleged the kill and rolling back its transaction.

SQL>select used_urec from v$transaction where addr = (select taddr from v$session where sid= and serial#=);

3. If there is no rolling back happening an session just seems to be stuck, then it's time to kill that session's process from OS level.
4. If couple of minutes after killing the process from OS level that session is still not released (no rollback), then attaching to the OS process with oradebug and run 'ORADEBUG WAKEUP 2' couple of times and check is the session is gone. The "2" means Oracle PID of PMON process which is usually 2, but you should check it from your v$process view.

SQL>oradebug setospid ;
SQL>oradebug wakeup 2;

5. If step 4 does not work, try to kill the oracle process using oradebug.

SQL>oradebug setospid ;
SQL>oradebug event immediate crash;