Automate Kill SNIPED SESSION Oracle Database

If you have configured IDLE_TIME inr your user profile.

IDLE_TIME 

Specify the permitted periods of continuous inactive time during a session, expressed in minutes. Long-running queries and other operations are not subject to this limit

Lets say a session has been idle for 10 Minutes. Session will continue to show as idle even after the idle_time for that user,as specified in that user's profile, has expired. When the user attempts to run a transaction against the database after the idle_time has expired, the database will disconnect the user by terminating the session. After this, the
session will no longer show in v$session. So, even if the session appears to be idle for a duration slightly more then your 10 minutes -- it is already "dead", it just doesn't show as dead yet. PMON will eventually snipe the session, marking it dead in v$session.

Reference this oracle Document for more information.

Once the oracle session is changed to SNIPED status, we can kill that session without any problem. How ever this be done manually, but watching for these SNIPED sessions every time can be irritating, we can automate the job of killing SNIPED session as below.


-- This is an optional table. Create this only if you want to Audit the killed session.

CREATE
  TABLE "AUDIT_KILL_SNIPED_SESSIONS"
  (
    "SID"        NUMBER,
    "SERIAL#"    NUMBER,
    "TIME_STAMP" TIMESTAMP (6) DEFAULT SYSTIMESTAMP,
    "USERNAME"   VARCHAR2(50 BYTE)
  )TABLESPACE "USERS" ;


Note :     Inorder to create below procedure you will need DBA role & "ALTER SYSTEM" grant. If you are creating this procedure in non DBA user, you will need grant select on V$SESSION & "ALTER SYSTEM" grant


-- Procedure to kill sessions that are in sniped state

create or replace Procedure KILL_SNIPED_SESSIONS as
cursor SEL_SID is select SID,SERIAL#,USERNAME from v$session where status='SNIPED';
SEL_REC SEL_SID%ROWTYPE;
V_SQL varchar2(100);
V_SQL1 varchar2(100);

Begin
OPEN SEL_SID;
LOOP
FETCH SEL_SID INTO SEL_REC;
EXIT WHEN SEL_SID%NOTFOUND;

V_SQL :='ALTER SYSTEM KILL SESSION '''||SEL_REC.SID||','||SEL_REC.SERIAL#||''' IMMEDIATE';
-- DBMS_OUTPUT.PUT_LINE(V_SQL);
execute immediate V_SQL;
--- LETS AUDIT THE KILLED SESSIONS DATA HERE
-- COMMENT LINES BELOW THIS TO REMOVE AUDITING

V_SQL1:= 'insert INTO AUDIT_KILL_SNIPED_SESSIONS (SID,SERIAL#,USERNAME) VALUES ('''||SEL_REC.SID||''','''||SEL_REC.SERIAL#||''','''||SEL_REC.USERNAME||''')';
-- DBMS_OUTPUT.PUT_LINE(V_SQL1);
COMMIT;
execute immediate V_SQL1;

-- COMMENT UNTILL THIS LINE TO REMOVE AUDITING

END LOOP;
CLOSE SEL_SID;

END;


-- YOU CAN ALSO SETUP A JOB TO RUN EVERY 30 MINS OR HOUR TO EXECUTE THIS PROCEDURE

BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
            job_name => '"EXEC_KILL_SNIPED_SESSIONS"',
            job_type => 'STORED_PROCEDURE',
            job_action => 'KILL_SNIPED_SESSIONS',
            number_of_arguments => 0,
            start_date => TO_TIMESTAMP_TZ('2016-09-29 13:07:38.837143000 AMERICA/CHICAGO','YYYY-MM-DD HH24:MI:SS.FF TZR'),
            repeat_interval => 'FREQ=HOURLY',
            end_date => NULL,
            enabled => FALSE,
            auto_drop => FALSE,
            comments => 'Job to run KILL_SNIPED_SESSIONS procedure that kills SNIPED SESSIONOS ');

       
    DBMS_SCHEDULER.SET_ATTRIBUTE(
             name => '"EXEC_KILL_SNIPED_SESSIONS"',
             attribute => 'logging_level', value => DBMS_SCHEDULER.LOGGING_OFF);
     
 
   
    DBMS_SCHEDULER.enable(
             name => '"EXEC_KILL_SNIPED_SESSIONS"');
END;


References :

http://arvindasdba.blogspot.com/2016/09/alter-system-kill-session.html