Allow Oracle Users to Remove Their Own Locks

Somehow your oracle users have an uncanny ability to leave locks on their tables making them inaccessible for an unusually long time. This could happen due to poor commit practises or simply because a remote session timed out at the wrong instant. You would like to avoid having the issue escalated by giving the user the authority to remove the locks themselves. We can do this using a stored procedure to kill the offending sessions.

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;