The desired procedure should accomplish the following:
- User can kill only those sessions they own. In other words they can't kill other user's sessions if they happen to know other user ids.
- Kills will occur in sessions the user holds other than the current session. When they are connected (or reconnected) in an attempt to perform the unlock we don't want to kill the session they are currently using.
Logged in as a user that has ALTER SYSTEM privileges create the following stored procedure.
create or replace procedure selfkillSessionProc IS
strNeeded VARCHAR2(50);
cursor_name pls_integer
default dbms_sql.open_cursor;
ignore
pls_integer;
BEGIN
FOR x in (SELECT s.inst_id,
s.sid,
s.serial#,
p.spid,
s.username,
s.program
FROM gv$session s
JOIN gv$process p ON
p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE s.type != 'BACKGROUND' AND s.username in (SELECT
SYS_CONTEXT ('USERENV', 'SESSION_USER') FROM DUAL) AND
s.sid NOT IN (select sid from
V$SESSION where AUDSID = userenv('SESSIONID')))
LOOP
DBMS_OUTPUT.PUT_LINE(x.sid || ','
|| x.serial#);
strNeeded := '''' || x.sid || ','
|| x.serial# || '''' ;
DBMS_OUTPUT.PUT_LINE(strNeeded);
dbms_sql.parse(cursor_name,
'alter system kill session '''
||x.sid||','||x.serial#||'''',
dbms_sql.native);
ignore :=
dbms_sql.execute(cursor_name);
END LOOP;
END;
The above procedure ultimately uses the ALTER SYSTEM command to end the current user's other sessions. To do this we need to gather the session IDs and the session serial numbers from the V$SESSION table.
Additionally, the ALTER SYSTEM command cannot be called directly from a stored procedure so we use a DBMS parser to run the command.
Finally, give your users permission to use the stored procedure.
grant execute on selfkillSessionProc to public;
