Patrice,
I
think I posted this not so long ago but I have this one and it works.
Regards & HTH
Lee
/*******************************************************/
/* */
/* Author: Lee Robertson */
/* */
/* Title: killsession.sql */
/* */
/* Date: 19/04/01 */
/* */
/* Description: Enable a non-DBA user to kill their */
/* own session but not the current one. */
/* */
/*******************************************************/
/* */
/* Author: Lee Robertson */
/* */
/* Title: killsession.sql */
/* */
/* Date: 19/04/01 */
/* */
/* Description: Enable a non-DBA user to kill their */
/* own session but not the current one. */
/* */
/*******************************************************/
create or replace procedure
killsession
(v_sid IN number, v_serial IN number, v_puser IN varchar2)
as
(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;
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);
dbms_sql.parse(v_cursor_name, v_statement, dbms_sql.v7);
v_rows_processed :=
dbms_sql.execute(v_cursor_name);
dbms_sql.close_cursor(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;
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-----
From: Boivin, Patrice J [mailto:[EMAIL PROTECTED]]
Sent: 29 August 2002 21:19
To: Multiple recipients of list ORACLE-L
Subject: Letting users kill their own sessionsDoes anyone have a dynamic script that would let users kill their own sessions?I found something in Metalink but the script doesn't appear to verify whether the PID and SID supplied belongs to the person executing the procedure... not ideal.Thanks.
Regards,
Patrice Boivin
Systems Analyst (Oracle Certified DBA)Systems Admin & Operations | Admin. et Exploit. des systèmes
Technology Services | Services technologiques
Informatics Branch | Direction de l'informatique
Maritimes Region, DFO | Région des Maritimes, MPOE-Mail: [EMAIL PROTECTED]
*********************************************************************
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.