kill.sql

create or replace PROCEDURE sp_kill_session (pis_sid IN VARCHAR2,pis_ser IN VARCHAR2)
IS
/*
||     This SP will kill a session based on SID and SERIAL# or
||     kill all sessions for STATUS='ATC" and SCHEMANAME IS NULL if
||     both inputs are NULL.       
||    
||    Created By       :  John Doe
||    Ceated Date      :  01/20/1999
||    Modified History :
||                         
*/
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)      ='ACT'
AND   substr(schemaname,1,10) IS NULL;
ls_sql VARCHAR2(150);

BEGIN
IF (pis_sid IS NULL) AND (pis_ser IS NULL)
THEN
  FOR c1rec in c_session
  LOOP
  -- alter system kill session '12,18'
  ls_sql:='alter system kill session '||''''||C1REC.SID||','||C1REC.SER||'''';
  DYNSQL(LS_SQL,'');
END LOOP;
ELSE
  ls_sql := 'alter system kill session '||''''||pis_sid||','||pis_ser||'''';
  DYNSQL(LS_SQL,'');
  DBMS_OUTPUT.PUT_LINE(LS_SQL);
END IF;


END sp_kill_session;
/