grant ALTER SYSTEM to FIDELITYDBA;
grant SELECT on GV_$SESSION to FIDELITYDBA;
create or replace PROCEDURE FIDELITYDBA.kill_usersession(n_sid IN NUMBER, n_serial IN NUMBER, n_instance IN NUMBER default 1)
AS
BEGIN
  DECLARE
    v_myuser   VARCHAR2(30) DEFAULT '';
    v_killuser VARCHAR2(30) DEFAULT '';
  BEGIN
    SELECT sys_context('USERENV','SESSION_USER') INTO v_myuser FROM dual;
    SELECT username
      INTO v_killuser
      FROM gv$session
     WHERE sid     = n_sid
	   AND serial# = n_serial
       AND inst_id = n_instance;
    IF ( v_killuser=v_myuser ) THEN
	  EXECUTE immediate('ALTER SYSTEM KILL SESSION ''' || n_sid || ',' || n_serial || ',@' || n_instance || '''');
    ELSE
      raise_application_error(-20000, 'Username mismatch: You can not kill the session of another schema!');
    END IF;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
	  raise_application_error(-20001,'No session exists with provided details');
	WHEN OTHERS THEN
      raise_application_error(-20002,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
  END;
END kill_usersession;
/
grant execute on FIDELITYDBA.kill_usersession to public;
