29th May 2014 3 Comments
When you end up spending a far great a percentage of your day than seems sensible killing off Java connections that Developers have carelessly left lying around, locking objects all over the place, you need a solution to get them to go away. The solution is to let them do it themselves!
I’m not advocating granting ALTER SYSTEM to Developers! That way madness lies, or certainly some unintended consequences. I’m all for Dev’s having a lot of freedom in the database, just not freedom with the database.
So, creating a stored procedure (in this example as sys, but as any user with an explicit ALTER SYSTEM privilege granted will do) to kill sessions without allowing too much latitude to do anything else seems appropriate. Here’s one I built earlier:
create or replace procedure sys.kill_session ( p_sid IN number, p_serial IN number, p_instance IN number) as -- Neil Chandler. Grant the ability to kill session on a restricted basis. 21.07.2010 l_username varchar2(30) := null; l_priv number := 1; begin -- Who owns the session? select username into l_username from gv$session where sid = p_sid and serial#=p_serial and inst_id = p_instance; -- Check for DBA role select count(*) into l_priv from dba_role_privs where grantee = l_username and granted_role = 'DBA'; -- If the user has the DBA priv, deny the kill request if l_priv > 0 or l_username is null then dbms_output.put_line ('User request to kill session '||p_sid||','||p_serial||',@'||p_instance|| ' denied. Session is for privileged user '||l_username||'.'); else dbms_output.put_line ('Killing user '||l_username||' - '||p_sid||','||p_serial||',@'||p_instance); execute immediate 'alter system disconnect session '''|| p_sid||','||p_serial||',@'||p_instance|| ''' immediate'; end if; end; / -- and let the proc be seen and used create or replace public synonym kill_session for sys.kill_session; grant execute on kill_session to (whomever); Then a nifty bit of sql to generate the kill commands for the Developers. Please include your favourite columns from gv$session: select username,status,blocking_session, 'exec kill_session ('|| sid||','||serial#||','||inst_id||')' Kill_Command from gv$session where username is not null and type <> 'BACKGROUND' / USERNAME STATUS BLOCKING_SESSION KILL_COMMAND --------- ------- ---------------- ------------------------------------ SYS ACTIVE exec kill_session (31,65,1) SYSTEM INACTIVE exec kill_session (58,207,1) USER_1 INACTIVE exec kill_session (59,404,1) USER_2 INACTIVE exec kill_session (72,138,1) USER_2 INACTIVE exec kill_session (46,99,2)
May the odds be forever in your favour. Let the killing commence…