Wednesday, September 18, 2013

oracle KILL MY OWN SESSION for developers (without system privilege )

It is a common  request from development to kill some run-away sessions so that they can re-run the application or query.. I have created a procedure to kill your own session. This way,developers can be more productive and not wait for a DBA to do the hunting..

The checks put into the procedure are that it has to run from the same username and from the same laptop as the session you want to kill.

How to run
____________

set serveroutput on -- (for debug info )
exec kill_my_session(183,
6963);

  

The two parameter passed are SID and SERIAL# from gv$session. This procedure can kill across instances in RAC cluster as well.

SAMPLE OUTPUT
--------------------
anonymous block completed
the procedure got executed by SCOTT-vijay-PC and sid,serial is from SCOTT-vijay-PC instance 1
they are the same user
successful in executing alter system kill session '123,321,@1';

-----
Also, below is a query you could use to see the info from gv$session. One of the column has the kill_my_session() info... you can simply cut and paste :)
This is a handy script to have if you need to kill your session which spawned multiple parallel query slaves and you need to kill the query's main session.
I have not concentrated on formatting since you could use some development tool like sql*developer.


SCRIPT to find you session
-------------------------------
SELECT s.inst_id,
s.program,
s.module,
s.event,
s.username,
s.SQL_ID,
TO_CHAR(sysdate,'SSSSS')- TO_CHAR(sql_exec_start,'SSSSS') sec_since_sqlStart,
machine,
'exec kill_my_session(' ||sid ||',' ||serial# ||');' kill_My_session ,
lpad( TO_CHAR( TRUNC(24 *(sysdate-s.logon_time)) ) || TO_CHAR(TRUNC(sysdate) + (sysdate-s.logon_time) , ':MI:SS' ) , 10, ' ') AS UP_time ,
px.count_pq,
sq.sql_text
FROM gv$session s,
(SELECT qcsid ,COUNT(*) count_pq FROM gv$px_session GROUP BY qcsid
) px ,
v$sqlarea sq
WHERE s.type! ='BACKGROUND'
AND s.status ='ACTIVE'
AND s.sql_id IS NOT NULL
AND s.program NOT LIKE '%(P%)%'
AND s.sql_id =sq.sql_id(+)
AND s.sid =px.qcsid(+);

---------------------
The actual kill_my_session is a procedure owned by a schema with DBA privileges with a public synonym.
you also grant execute on the procedure to public;

create or replace
procedure kill_my_session ( sid_v in number, serial in number ) as
run_by varchar2(32);
sess_user varchar2(32);
inst  number;
my_machine varchar2(32);
sess_machine varchar2(32);
begin
SELECT SYS_CONTEXT ('USERENV', 'SESSION_USER') , SYS_CONTEXT ('USERENV', 'HOST')
   into run_by , my_machine FROM DUAL;
   begin
   select username ,inst_id,machine into sess_user,inst,sess_machine from gv$session where sid=sid_v and serial#= serial;
   exception when no_data_found then
   dbms_output.put_line('no session like that in db');
   end;
  dbms_output.put_line('the procedure got executed by '||run_by||'-'||my_machine||' and sid,serial is from '||sess_user||'-'||sess_machine||' instance '||inst);
   if (run_by=sess_user) and (my_machine=sess_machine) then
     dbms_output.put_line('they are the same user');
     begin
     execute immediate 'alter system kill session '''||sid_v||','||serial||',@'||inst||'''';
     dbms_output.put_line(' successful in executing alter system kill session '''||sid_v||','||serial||',@'||inst||''';');
     exception when others then
     dbms_output.put_line(' error executing alter system kill session '''||sid_v||','||serial||',@'||inst||'''');
     dbms_output.put_line(SUBSTR(SQLERRM(SQLCODE), 1, 250));
     end;
   else
     dbms_output.put_line('cannot kill another user''s session');
  end if;
end;

CREATE OR REPLACE PUBLIC SYNONYM "KILL_MY_SESSION" FOR "<DBA_OWNER>"."KILL_MY_SESSION";

grant execute on KILL_MY_SESSION to PUBLIC;

No comments:

Post a Comment

Feedback welcome