Oracle DBA – A lifelong learning experience

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)
killserial   varchar2(20);

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) || '''' ;

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


Security issues

  • Why do they need to kill jobs, what is causing the issue
    What will developers want next
    Sarbanes-Oxley considerations


  • 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.


2 Responses to “Procedure to kill a session”

  1. Michael A. Rife said

    Another consideration is that even though the session is marked as killed in the database does not really mean it is completely killed. Sometimes the OS process on the database server has to be killed as well before it will end and release all the resources it has locked!!

  2. […] 4- Using alter system kill session in procedure John Hallas-Procedure to kill a session […]

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: