Re: killing oracle processes

From: Tim Gorman <>
Date: Mon, 17 Aug 2009 11:51:48 -0600
Message-ID: <>



Regardless of the political/security implications, here is a stored procedure to do what you're requesting.  As you can see, it's pretty ancient (probably dates back to the initial release of PL/SQL and DBMS_SQL in v7.0.x timeframe), so there is *definitely* no warranty on this...

Hope this helps...
Tim Gorman
consultant - Evergreen Database Technologies, Inc.
P.O. Box 630791, Highlands Ranch CO  80163-0791
website   =
email     =
mobile    = +1-303-885-4526
fax       = +1-303-484-3608
Yahoo IM  = tim_evdbt

Michael Schmitt wrote:


Hi All,


I had a quick question that I was hoping the list could help me out with.  We have a group of developers who are requesting the ability to kill their own processes in the database (PRD/DEV/TST).  For example, if a poorly written report gets kicked off, one of their jobs chooses a poor execution plan, or an OWB process gets left out there.  The only reason they can really offer is that they do not have to wait for the DBA team to respond.  I am trying to think of technical reasons why this would not work.


I can write a script to limit the process to be killed to their stuff, but something about this still makes me feel uneasy.  Is there anything that I should worry about? 


Any thoughts?        



 * File:        kill_session.sql
 * Type:        SQL*Plus script
 * Author:      Tim Gorman (Evergreen Database Technologies, Inc.)
 * Date:        05-Jun-96
 * Description:
 *      SQL*Plus script to create the KILL_SESSION stored procedure,
 *	which is intended to allow any Oracle account to kill other
 *	sessions under the same Oracle account...
  • Modifications: *********************************************************************/ whenever oserror exit failure whenever sqlerror exit failure

set echo on feedback on timing on

spool kill_session

show user
show release

set termout off
create or replace procedure kill_session (in_sid in number, in_serial in number) is

	errcontext		varchar2(200);
	errmsg			varchar2(200);
	v_string		varchar2(30);
	v_username		varchar2(30);
	v_cursor		integer;
	rows_processed		integer;
	marked_for_kill		exception;
	pragma			exception_init(marked_for_kill, -31);

errcontext := 'query V$SESSION';
select	to_char(sid) || ',' || to_char(serial#),
into	v_string,
from	v$session
where	sid = in_sid
and	serial# = in_serial;
if v_username <> user then
	raise_application_error(-20002, user || ' cannot kill ' ||
				v_username || '''s session.');
end if;
errcontext := 'dbms_sql.open_cursor';
v_cursor := dbms_sql.open_cursor;
errcontext := 'dbms_sql.parse';
		'ALTER SYSTEM KILL SESSION ''' || v_string || '''',
errcontext := 'dbms_sql.execute';
rows_processed := dbms_sql.execute(v_cursor);
errcontext := 'dbms_sql.close_cursor';
dbms_output.put_line('Session "' || v_string || '" for "' ||
		     v_username || '" killed.');
	when no_data_found then
		raise_application_error(-20003, 'No such SID,SERIAL# found.');
	when too_many_rows then
		raise_application_error(-20004, 'Unique SID,SERIAL# required.');
	when marked_for_kill then
		dbms_output.put_line('Session "' || v_string || '" for "' ||
					v_username || '" marked for kill...');
	when others then
		errmsg := sqlerrm;
		raise_application_error(-20001, errcontext || ': ' || errmsg);
end kill_session;
set termout on
show errors

whenever sqlerror continue
drop public synonym kill_session
whenever sqlerror exit failure

create public synonym kill_session for kill_session

grant execute on kill_session to public

spool off

Received on Mon Aug 17 2009 - 12:51:48 CDT

Original text of this message