Procedure to kill a session
Posted by John Hallas on August 18, 2009
On the Oracle-L mailing list a user requested a procedure to allow a developer to kill his own process. This contributed quite a few comments which I will list below and I posted a method that I use when I have the same requirement.
To create the stored procedure logon as a sysdba through sqlplus and explicitly grant the required rights to system and then create the procedure.
grant alter system to system; grant select on sys.v_$session to system; create or replace procedure system.killsession (killsid IN VARCHAR2) as killserial varchar2(20); begin killserial:='none'; select serial# into killserial from sys.v_$session where sid = killsid and username = 'WES'; execute immediate 'alter system kill session ''' || to_char (killsid) || ', ' || to_char (killserial) || '''' ; EXCEPTION WHEN NO_DATA_FOUND THEN dbms_output.put_line('The SID ' || killsid || ' does not exist or cannot be killed'); end ;
Grant execute on the procedure to the user
For example, userxxx needs access to kill sessions so you would type the following:-grant execute on system.killsession to userxxx;
The user needs to know the sid of the session being killed. For example, if a user needs to kill a session with a SID of 140 they would type:-set serveroutput on exec system.killsession ('140');
If they get the following error then there is no SID with a username of WES currently connected:-The SID 140 does not exist or cannot be killed
The various thoughts on allowing such a procedure centred on two strands, security and practical considerations
- Why do they need to kill jobs, what is causing the issue
What will developers want next
- How do you know the developer will kill one of his own processes and not a co-worker (think apps or sysadm job)
Can you use resource profiles to limit CPU usage
Use a standby to allow unrestricted resource usage
My view is that in certain development environments, for certain users it is reasonable to allow them to kill a process that is running rogue and as such I am happy to provide such a facility. This would not be allowed either unrestricted or in any sort of controlled environment such as production and pre-production.