Oracle-L: RE: Letting users kill their own sessions

From: Robertson Lee - lerobe <lerobe_at_acxiom.co.uk>
Date: Fri, 30 Aug 2002 01:23:23 -0800
Message-ID: <F001.004C3C76.20020830012323@fatcity.com>

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.   */
/*                                                     */
/*******************************************************/

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);

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: 29 August 2002 21:19
To: Multiple recipients of list ORACLE-L

Does 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, MPO

E-Mail: boivinp_at_mar.dfo-mpo.gc.ca

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: lerobe_at_acxiom.co.uk

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: ListGuru_at_fatcity.com (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). Received on Fri Aug 30 2002 - 04:23:23 CDT