Re: killing oracle processes

Michael,

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   = http://www.EvDBT.com/
email     = Tim@xxxxxxxxx
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?        

 

Thanks

/**********************************************************************
 * 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);
        --
begin
--
errcontext := 'query V$SESSION';
select  to_char(sid) || ',' || to_char(serial#),
        username
into    v_string,
        v_username
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';
dbms_sql.parse(v_cursor,
                'ALTER SYSTEM KILL SESSION ''' || v_string || '''',
                dbms_sql.v7);
--
errcontext := 'dbms_sql.execute';
rows_processed := dbms_sql.execute(v_cursor);
--
errcontext := 'dbms_sql.close_cursor';
dbms_sql.close_cursor(v_cursor);
--
dbms_output.put_line('Session "' || v_string || '" for "' ||
                     v_username || '" killed.');
--
exception
        --
        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

Other related posts: