Useful shortcuts for vi editor

Friday 10 October 2014

How to kill a session with custom procedure?

-If you are authorized to sys user.

Firstly run this procedure to compile and save.
---
CREATE OR REPLACE PROCEDURE P_KILL_SESSION(DATABASE_USER_NAME IN VARCHAR2, STR_RESULT OUT VARCHAR2)
   IS
str_exec VARCHAR2(1000);
   BEGIN
     FOR cur_session IN (SELECT sid, serial#
                         FROM v$session
                        WHERE username = upper(DATABASE_USER_NAME)
                          AND status != 'KILLED') LOOP                          
      str_exec := 'alter system kill session ''' || cur_session.sid || '' || ',' || cur_session.serial# ||'''';      
      BEGIN
          execute immediate str_exec;
          STR_RESULT := 'Sessions are terminated successfully';
      EXCEPTION
      WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR ( -20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM ) ;
      END;     
   END LOOP;
END;
---

To use it;
In command window-editor,

var STR_RESULT VARCHAR2
EXEC P_KILL_SESSION('test_user',:STR_RESULT)
--test_user session could be killed