Search notes:

Oracle: Terminating (killing) sessions

Create a «demonstration» session (which will be killed) and identify its SID and SERIAL#: 
$ sqlplus …
SQL> select sid, serial# from v$session where sid = sys_context('userenv', 'sid');
--
--        SID    SERIAL#
-- ---------- ----------
--        130       1684
In another session, select some values related to the session and its associated process: 
select
   ses.status,
   ses.paddr,
   prc.pid,
   prc.spid
from
   v$session ses                                        left join
   v$process prc on ses.paddr           = prc.addr
where
   ses.sid     =   130 and
   ses.serial# =  1684;
--
-- STATUS   PADDR                   PID SPID                    
-- -------- ---------------- ---------- ------------------------
-- INACTIVE 00000000945DC598         85 5156
Still in the same session: kill the demonstration session: 
alter system kill session '130,1684';
--
-- System KILL altered
Check the session data again: 
select
   ses.status,
   ses.paddr,
   prc.pid,
   prc.spid
from
   v$session ses                                        left join
   v$process prc on ses.paddr           = prc.addr
where
   ses.sid     =   130 and
   ses.serial# =  1684;
--
-- STATUS   PADDR                   PID SPID                    
-- -------- ---------------- ---------- ------------------------
-- KILLED   00000000945DC598
The session is still here and marked as KILLED, but the process is gone (i. e. select * from v$process where addr = '00000000945DC598' returns nothing).
The demonstration session remains as KILLED until another statement is executed in it which gives Oracle an occasion to notify the user that the session is killed: 
SQL> select * from dual;
--
-- …
-- ERROR at line 1:
-- ORA-00028: your session has been killed
After delivering this notification, the session record is also gone in v$session, i. e. select * from v$session where sid = 130 and serial# = 1684 returns nothing.

ALTER SYSTEM KILL SESSION … FORCE

Similar to above, this time, a session is killed with the FORCE attribute: 
alter system kill session '130,4712' FORCE;
--
-- System KILL altered.
With this approach, the entry in v$session disappears without delivering ORA-00028 to the user.
The nex statement entered in the «demonstration» session is answered like so: 
SQ>> select * from dual;
…
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 5602
Session ID: 130 Serial number: 4712


ERROR:
ORA-03114: not connected to ORACLE

Index