>>From: Jacques Kilchoer
>>
>>Write a procedure that kills the relevant session(s) and grant the user
>>execute access to that procedure.
>>
>-----Original Message-----
>From: ef 8454 [mailto:[EMAIL PROTECTED]]
>
>Thank you for the answer. Do you have sample program?
The OWNER of the procedure will need the following privileges granted DIRECTLY, NOT via a role.
"select on sys.v_$session" or "select any table"
"alter system"
This procedure will allow you to kill any session that has the same username as your current username. The user EXECUTING the procedure will need some way to find sid and serial# for their various sessions, to know which one to kill. (For Oracle versions that don't support execute immediate, change the procedure to use dbms_sql)
create procedure kill_your_session (in_sid in sys.v_$session.sid%type,
in_serial# in sys.v_$session.serial#%type)
as
row_count pls_integer ;
begin
select count (*)
into row_count
from v$session
where username = user and sid = in_sid and serial# = in_serial# ;
if row_count > 0
then
execute immediate 'alter system kill session ''' ||
to_char (in_sid) || ', ' || to_char (in_serial#) || '''' ;
end if ;
end ;
/