How to kill SNIPED sessions dynamically using PL/SQL in Oracle

In Oracle, to kill sniped sessions dynamically using SQL, you can use EXECUTE IMMEDIATE inside a PL/SQL block.

Understanding Sniped Sessions

A sniped session is a session that has been marked as inactive (due to profile settings like IDLE_TIME in a resource profile), but it is still present in v$session and v$process. Such sessions must be explicitly killed using ALTER SYSTEM KILL SESSION or SYS.DBMS_SYSTEM.KILL_SESSION.

---

Procedure to Kill Sniped Sessions Dynamically

This procedure:

• Finds sessions in v$session with status = 'SNIPED'.
• Uses EXECUTE IMMEDIATE to kill them.

PL/SQL Code

CREATE OR REPLACE PROCEDURE kill_sniped_sessions IS
    CURSOR sniped_sessions IS
        SELECT 'ALTER SYSTEM KILL SESSION ''' || sid || ',' || serial# || ''' IMMEDIATE' AS kill_command
        FROM v$session
        WHERE status = 'SNIPED';

    v_sql VARCHAR2(200);
BEGIN
    FOR session_rec IN sniped_sessions LOOP
        v_sql := session_rec.kill_command;
        EXECUTE IMMEDIATE v_sql;
        DBMS_OUTPUT.PUT_LINE('Killed session: ' || v_sql);
    END LOOP;
END;
/

---

Alternative: Killing Using SYS.DBMS_SYSTEM.KILL_SESSION

Instead of ALTER SYSTEM KILL SESSION, you can use DBMS_SYSTEM.KILL_SESSION:

CREATE OR REPLACE PROCEDURE kill_sniped_sessions IS
    CURSOR sniped_sessions IS
        SELECT sid, serial#
        FROM v$session
        WHERE status = 'SNIPED';

BEGIN
    FOR session_rec IN sniped_sessions LOOP
        SYS.DBMS_SYSTEM.KILL_SESSION(session_rec.sid, session_rec.serial#);
        DBMS_OUTPUT.PUT_LINE('Killed session: SID=' || session_rec.sid || ', SERIAL#=' || session_rec.serial#);
    END LOOP;
END;
/

---

Automating with a Scheduled Job

You can automate sniped session killing using DBMS_SCHEDULER:

BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
        job_name        => 'AUTO_KILL_SNIPED_SESSIONS',
        job_type        => 'PLSQL_BLOCK',
        job_action      => 'BEGIN kill_sniped_sessions; END;',
        start_date      => SYSTIMESTAMP,
        repeat_interval => 'FREQ=HOURLY; INTERVAL=1', -- Run every hour
        enabled         => TRUE
    );
END;
/

---

or

Alternatively you can automate sniped session killing using DBMS_JOB also:

declare

jobno number;

begin

dbms_job.submit(jobno, 'kill_sniped_sessions;',sysdate,'TRUNC(SYSDATE,''mi'')+5/1440'); -- Run every 5 minutes

end;

/

Key Takeaways

1. Identify sniped sessions from v$session where status = 'SNIPED'.
2. Use EXECUTE IMMEDIATE to dynamically kill sessions.
3. Ensure you have ALTER SYSTEM privileges to kill sessions.
4. Consider automating this process using DBMS_SCHEDULER.
5. Grant select on v_$session must be given to the user if run other than SYS user