The command to kill Oracle is simple:
ALTER SYSTEM KILL SESSION '&sid,&serial';
sid – session ID
serial – session serial number
If the version 11g and this is RAC, additional syntax is possible:
ALTER SYSTEM KILL SESSION '&sid,&serial,@&inst_id';
inst_id – instance id number (1, 2, 3 …)
Script to find the TOAD session older than 1 day and kill them:
select
'ALTER SYSTEM KILL SESSION ''' || sid || ',' || serial# || ''' immediate;' CMD
from v$session
where
program like '%TOAD%'
and logon_time and status != 'KILLED'
;
If the session is killed on the Oracle level and still stays in the list for very long time with status ‘KILLED’, it could be killed on the OS level.
Warning! This is potentially dangerous operation! Use it only as the last resort.
select sess.sid, sess.serial#,
'OS:' || proc.spid SPID,
'App:' || sess.process process,
substr(sess.username,1,15) username ,
sess.program,
logon_time ,
sess.status
from v$session sess, v$process proc
where sess.paddr = proc.addr
and sess.status = 'KILLED'
order by 1,2;
Check if “App:” process still exist – maybe, it’s worth to stop the application first.
Otherwise, kill the “OS:” process.