Re: killing oracle processes
- From: Tim Gorman <tim@xxxxxxxxx>
- To: mschmitt@xxxxxxxxxxxx
- Date: Mon, 17 Aug 2009 11:51:48 -0600
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:
