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

 

Security issues

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

Practical

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

Advertisements

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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

 
%d bloggers like this: