Useful shortcuts for vi editor

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