kill_session.sql

declare
CURSOR c_session IS
SELECT substr(s.sid,1,3) sid,substr(s.serial#,1,5) ser
FROM  v$process p, v$SESSTAT t,v$sess_io i ,v$session s
WHERE i.sid                   = s.sid  
AND   p.addr                  = paddr(+) 
AND   s.sid                   = t.sid
AND   t.statistic#            = 12
--AND   substr(status,1,3)      ='INA'
AND   substr(schemaname,1,10) ='JSMITH';
ls_sql VARCHAR2(150);

BEGIN
--IF (pis_sid IS NULL) AND (pis_ser IS NULL)
--THEN
  FOR c1rec in c_session
  LOOP
  declare
  begin
  -- alter system kill session '12,18'
  ls_sql:='alter system kill session '||''''||C1REC.SID||','||C1REC.SER||'''';
  EXECUTE IMMEDIATE ls_sql;
  exception
  when others
  then
   null;
  end;
END LOOP;
--END IF;
END;
/