Re: "kill" equivalent for ORACLE?

badri wrote:
>
> I want to provide an interface that would allow
> a user to kill their own oracle session, but not those of the others.
> If I grant 'alter system' privilege, it allows a user to
> kill any other user's process.
> Any thoughts would be greatly appreciated,
>
> thanks a lot,
>
> -Badri

We wrote a procedure which kills all other sessions for a user (other than their current one). Our applications call it when the user connects to be sure any orphan sessions are removed. The procedure is owned by a system userid which has the ALTER SYSTEM privilege. Any user can call it to kill THEIR other sessions. The code follows:

create procedure spr_s_remove_users as
/*
This procedure kills all other sessions for the calling user. Note that the owner of this procedure must have been explicitly granted the system privilege ALTER SYSTEM. */
id1 varchar2(30);
c1 integer;
sid2 number;
serial2 number;
cursor c2 is select sid, serial# from v$session   where username=user
  and sid <> (select sid from v$session where   userenv('SESSIONID') = audsid);
return1 integer;
begin
open c2;
loop
fetch c2 into sid2,serial2;
exit when c2%notfound;
c1:=dbms_sql.open_cursor;
dbms_sql.parse(c1,'alter system kill session ''' || to_char(sid2) || ',' || to_char(serial2) || '''',dbms_sql.v7); return1 := dbms_sql.execute(c1);
dbms_sql.close_cursor(c1);
end loop;
end;

Hope this helps.

-- 
Roger Harris
Oracle DBA
GE Credit Corp
Danbury, CT