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; /