ALTER SYSTEM KILL SESSION

Note : Killing sessions can be very destructive if you kill the wrong session, so be very careful when identifying the session to be killed.

 

There could be a number of reasons to kill non-essential Oracle user processes. In Oracle the alter system kill session command allows us to kill these Oracle sessions. 

The alter system kill session command requires two unique arguments that uniquely identify the Oracle session, the session identifier and serial number.

First you have to identify the session to be killed with alter system kill session.

select SID,SERIAL#,STATUS,SCHEMANAME,PROGRAM from v$session;


The SID and SERIAL# values of the Oracle session to be killed can then be substituted and the alter system kill session command issued.

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#';


Sometimes Oracle is not able to kill the session immediately with the alter system kill session  command alone. Upon issuing the alter system kill session  command, the session will be 'marked for kill'. It will then be killed as soon as possible.


In the case of a session being 'marked for kill' after issuing the alter system kill session  command and not killed immediately, the alter system kill session  command can be forced by adding the immediate keyword:

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE; 

In a RAC environment, you optionally specify the INST_ID, shown when querying the GV$SESSION view. This allows you to kill a session on different RAC node.

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_id';

References :

• Automate kill SNIPED sessions
•  maximum number of processes exceeded

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