Thursday, October 26, 2017

Kill orphaned DB connections

BEGIN
   FOR i
      IN (SELECT sid, serial# serial
            FROM V$SESSION s
           WHERE     s.STATUS = 'INACTIVE'
                 AND username = 'SCPOMGR'
                 AND OSUSER = 'scpoadm'
                 AND SQL_ID = '3m07b861v6tws'
                 AND PROGRAM = 'JDBC Thin Client')
   LOOP
      EXECUTE IMMEDIATE
         'ALTER SYSTEM KILL SESSION ''' || i.sid || ',' || i.serial || '''';

      DBMS_OUTPUT.put_line ('Session killed :' || i.sid || ',' || i.serial);
   END LOOP;
END;
/

No comments:

Post a Comment