clean lock session

find locked session

SELECT SID, serial, seconds_in_wait,
        oracle_username, os_user_name, object_name object_locked,locked_mode
      FROM   (SELECT   sess.SID, sess.serial# serial, lo.oracle_username,
                       lo.os_user_name, ao.object_name, lo.locked_mode,
                       sess.seconds_in_wait
                  FROM v$locked_object lo, dba_objects ao, v$session sess
                 WHERE ao.object_id = lo.object_id
                       AND lo.session_id = sess.SID
              ORDER BY seconds_in_wait DESC)


clean lock session

DECLARE
   v_sid                           NUMBER;
   v_serial                        NUMBER;
   v_seconds_in_wait               NUMBER;
   v_stmt                          VARCHAR2 (500);
   v_lock_time_wait_sec   CONSTANT NUMBER (3)     := 100;

   CURSOR c_data
   IS
      SELECT SID, serial, seconds_in_wait
        /*oracle_username, os_user_name, object_name object_locked,locked_mode, seconds_in_wait*/
      FROM   (SELECT   sess.SID, sess.serial# serial, lo.oracle_username,
                       lo.os_user_name, ao.object_name, lo.locked_mode,
                       sess.seconds_in_wait
                  FROM v$locked_object lo, dba_objects ao, v$session sess
                 WHERE ao.object_id = lo.object_id
                       AND lo.session_id = sess.SID
              ORDER BY seconds_in_wait DESC)
       WHERE ROWNUM <= 5;
BEGIN
   FOR xx IN c_data
   LOOP
      DBMS_LOCK.sleep (1);

      BEGIN
         v_stmt :=
               'alter system kill session '
            || CHR (39)
            || xx.SID
            || ','
            || xx.serial
            || CHR (39);

         IF xx.seconds_in_wait > v_lock_time_wait_sec
         THEN
            EXECUTE IMMEDIATE v_stmt;
         END IF;
      EXCEPTION
         WHEN OTHERS
         THEN
            NULL;
      END;
   END LOOP;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      NULL;
   WHEN OTHERS
   THEN
      raise_application_error (-20001, SQLERRM);
END;