kill oracle session using loop within PL/SQL or SQL

Question

we have this query:

ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

I need to kill all sessions which have the same SQL ID

I'm not sure how to connect things together, but I have this so far:

for rec in (SELECT se.sid,ss.serial# 
              FROM v$session ss, v$sesstat se, v$statname sn 
             WHERE se.statistic# = sn.statistic# 
               AND name like '%CPU used by this session%' 
               AND se.sid = ss.sid 
               AND ss.status = 'ACTIVE' 
               AND ss.username is not null 
               AND ss.sql_id ='f7frew3erwe'
             ORDER BY value ASC) loop
  ALTER SYSTEM KILL SESSION 'rec.sid,rec.serial#' IMMEDIATE; //this is the tricky part!
end loop;

Any suggestions?

Possible duplicate of http://stackoverflow.com/questions/55342/how-can-i-kill-all-sessions-connecting-to-my-oracle-database, though not quite. The top answer should give you all the information you need to finish though. From your code you only need to query `v$session` as well... — Ben, Apr 10 '12 at 11:31

score 9 · Accepted Answer · edited May 23 '17 at 11:44

The question is similar to How can I kill all sessions connecting to my oracle database?, though for not for all sessions.

You need to use execute immediate in order to alter the system in a PL/SQL block:

execute immediate 'Alter System Kill Session '''|| rec.Sid  
                       || ',' || rec.Serial# || ''' IMMEDIATE';

I query your need to use all the additional system tables. Something like the following query may suffice if all you want to kill is a specific SQL_ID:

SELECT se.sid,ss.serial# 
  FROM v$session
 WHERE status = 'ACTIVE' 
   AND username is not null 
   AND sql_id ='f7frew3erwe'

kill oracle session using loop within PL/SQL or SQL

1 Answers1