I had a similar problem a while ago now and the users in question could see
their session from toad but didn't have permissions to kill them. This is
what I did (see procedure below).
It allows a user to kill a session of theirs but not the current one. Here
is the code. All the user needs to know is his sid and serial number (gets
this form Toad).
I'm sure you could manipulate this code to suit your needs.
HTH
Lee
create or replace procedure killsession
(v_sid IN number, v_serial IN number, v_puser IN varchar2)
as
v_statement varchar2(200);
v_user varchar2(10);
v_cursor_name integer;
v_rows_processed integer;
BEGIN
dbms_output.enable(10000); -- Set up output buffer
-- get the details to kill
select username into v_user from v$session where v_sid = sid and v_serial =
serial# and v_puser = user;
-- format the statement to run
v_statement := 'alter system kill session '''||v_sid||','||v_serial||'''';
-- set up cursor and run the staement
v_cursor_name := dbms_sql.open_cursor;
dbms_sql.parse(v_cursor_name, v_statement, dbms_sql.v7);
-- Return value from dbms_sql.execute on DDL is undefined, don't check
v_rows_processed := dbms_sql.execute(v_cursor_name);
dbms_sql.close_cursor(v_cursor_name);
-- exception section where details entered are incorrect
exception
when no_data_found
then
dbms_output.put_line('One or more of your parameters
is wrong. Please Check again');
when others
then
raise;
-- end of procedure
end;
/
----Original Message-----
Sent: 11 July 2002 19:51
To: Multiple recipients of list ORACLE-L
We have developers who occassionally want to be able to kill their
sessions in a dev instance. We are concerned about giving them alter
system priviledge and were wondering what people do about this. Is there
any other way they can do this? Do some shops allow certain users to do
this with some training?
Thanks.
Joe
[EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Joe Armstrong-Champ
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
*********************************************************************
The information contained in this communication is
confidential, is intended only for the use of the recipient
named above, and may be legally privileged.
If the reader of this message is not the intended
recipient, you are hereby notified that any dissemination,
distribution, or copying of this communication is strictly
prohibited.
If you have received this communication in error,
please re-send this communication to the sender and
delete the original message or any copy of it from your
computer system. Thank You.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Robertson Lee - lerobe
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
