Restricted Kill Session /* rkillsession (restricted kill session script): allows an oracle user knowing his username (Oracle account) to kill his own session from a new window running sqlplus but he can't do anything beyond that. The following has to be done before you can execute rkillsession (assuming you create the procedure in system schema): */ conn sys grant select on v_$session to system; grant alter system to system; conn system create or replace procedure rkillsession (sidn number, serial#n number) --restricted kill session; kill the same user's session from a new sqlplus session as currentuserv varchar2(30) := user; usernamev varchar2(30); currentosuserv varchar2(15) := sys_context('userenv', 'os_user'); osuserv varchar2(15); begin select username,osuser into usernamev,osuserv from v$session where sid = sidn and serial# = serial#n; --Criterion: username,osuser of the session to be killed must be --the same as the current user's username,osuser if (usernamev != currentuserv or osuserv != currentosuserv) then dbms_output.put_line('Session is not killed because'); dbms_output.put_line('(1) you entered wrong sid and serial# numbers;'); dbms_output.put_line('(2) you have to login from the same OS as the OS ' || 'of the session to be killed (e.g. you can''t kill a UNIX sqlplus ' || 'session from a Windows SQL*Plus session);'); dbms_output.put_line('(3) the session already exited.'); return; end if; execute immediate 'alter system kill session ''' || sidn || ',' || serial#n || ''''; dbms_output.put_line('The session you owned is successfully killed!'); end; / grant execute on rkillsession to &user_needs_to_do_this; -- Regular users now can kill his own session from a new sqlplus session conn scott/tiger set pages 100 serverout on feed off col "Username" for a8 col osuser for a8 col machine for a13 col terminal for a10 col "Client Program" for a14 /* --If this user (scott) can query v$session, perhaps by grant of select_catalog_role, --he can identify his other session he wishes to kill: select sid, serial#, username "Username", status, osuser, machine, terminal, program "Client Program", to_char(logon_time, 'yyyymmdd hh24:mi:ss') "Logon Time", last_call_et "Idle Time" from v$session where username = user; --otherwise, get the info elsewhere about his other session */ exec system.rkillsession(&sid, &serial) Note that this procedure is used to kill sessions in the local instance. If you run RAC and Oracle is older than 11gR1, you have to let the user connect to each of the RAC instances to kill his sessions. In 11gR1 or newer, the procedure can be modified to take advantage of the 3rd argument to 'alter system kill session', the instance ID, e.g. alter system kill session '123,456,@2';