How To Kill Session Of PLSQL Objects ?

• First we have to see how many sessions are running on a particular package/procedure/function using the below SQL.

    SELECT *

      FROM v$sqltext

     WHERE hash_value =

             (SELECT sql_hash_value

                FROM v$session

              WHERE SID = (SELECT SID

                            FROM v$access

                           WHERE OBJECT = 'XX_PKG' 
                              AND owner = 'APPS'));

• Get the session details which are accessing the code

    SELECT SID, serial#, sql_hash_value

      FROM v$session s, v$access a

     WHERE s.SID = a.SID

     AND a.OBJECT = 'XX_PKG';

• Now you have both the SID and SERIAL# for the session. You can identify the session uniquely and kill it.

    ALTER SYSTEM KILL SESSION 'SID_VALUE, SERIAL#_VALUE';