Useful shortcuts for vi editor

Showing posts with label inactive session. Show all posts
Showing posts with label inactive session. Show all posts

Monday 20 February 2017

How to kill all inactive sessions with custom procedure?

CREATE OR REPLACE PROCEDURE P_KILL_ALL_INACTIVE_SESSIONS
IS
   str_exec   VARCHAR2 (10000);
BEGIN
   FOR cur_session
      IN (SELECT DISTINCT
                    'ALTER SYSTEM KILL SESSION '''
                 || b.sid
                 || ','
                 || b.serial#
                 || ''''
                    AS KILL_THEM_ALL
            FROM sys.dba_ddl_locks a, sys.v_$session B
           WHERE     B.SID = a.session_id
                 AND owner NOT IN ('SYS',
                                   'WMSYS',
                                   'CTXSYS',
                                   'DBSNMP')
                 AND B.TYPE <> 'BACKGROUND'
                 AND B.status = 'INACTIVE')
   LOOP
      BEGIN
         str_exec := cur_session.KILL_THEM_ALL;
         EXECUTE IMMEDIATE str_exec;
      END;
   END LOOP;
END;
/

Usage:
BEGIN
P_KILL_ALL_INACTIVE_SESSIONS;
END;
/

Ref: https://ozsoyler.blogspot.com/2014/10/how-to-kill-session-with-custom.html