web analytics

Oracle: kill session

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.
 

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

  

  

  

Categories

A sample text widget

Etiam pulvinar consectetur dolor sed malesuada. Ut convallis euismod dolor nec pretium. Nunc ut tristique massa.

Nam sodales mi vitae dolor ullamcorper et vulputate enim accumsan. Morbi orci magna, tincidunt vitae molestie nec, molestie at mi. Nulla nulla lorem, suscipit in posuere in, interdum non magna.