SET SERVEROUTPUT ON
DECLARE
SNIPE_COUNT NUMBER (9);
CURSOR SNIPE_CUR IS
SELECT USERNAME, SID, SERIAL#
FROM V$SESSION
WHERE STATUS = 'SNIPED';
SNIPE_REC SNIPE_CUR%ROWTYPE;
V_SQLSTRING VARCHAR2 (100);
BEGIN
dbms_output.enable(1000000);
SELECT COUNT (STATUS)
INTO SNIPE_COUNT
FROM V$SESSION
WHERE STATUS = 'SNIPED';
IF SNIPE_COUNT = 0 THEN
DBMS_OUTPUT.PUT_LINE ('THERE ARE NO SNIPED SESSIONS');
ELSE
FOR SNIPE_REC IN SNIPE_CUR LOOP
DBMS_OUTPUT.PUT_LINE ('SNIPED SESSION FOR '|| SNIPE_REC.USERNAME || ' WILL BE KILLED');
V_SQLSTRING := 'ALTER SYSTEM KILL SESSION '''|| SNIPE_REC.SID ||', '|| SNIPE_REC.SERIAL# ||''' ';
-- ALTERNATIVELY COMMENT OUT THE EXECUTE IMMEDIATELY COMMAND AND REMOVE THE COMMENT LINES FROM THE NEXT
-- "DBMS_OUTPUT... " LINE FOR A REPORT ONLY, AND THE ALTER SYSTEM STRING NEEDED TO KILL SPECIFIC SNIPES
--
DBMS_OUTPUT.PUT_LINE (V_SQLSTRING);
EXECUTE IMMEDIATE V_SQLSTRING;
END LOOP;
END IF;
END;
/