Oracle - Criação de procedure para matar sessões em lock que estão com status de INACTIVE

O problema que o cliente reporta que é em alguns momentos do dia era gerado lock do tipo TX em algumas tabelas, segue evidencia do AWR:

Seguindo a analise, foi identifique que o ofensor era sessão do tipo INACTIVE, segue exemplo:

Pelo relatório e pelo print, o bloqueio é enq: TX – row lock contention e o “culpado” está INACTIVE (ex.: w3wp.exe do IIS). Isso acontece quando a sessão parou de executar, mas ficou com a transação aberta (sem COMMIT/ROLLBACK) segurando row locks.

Criado uma procedure que fica buscando sessões em lock e com status de inactive, e encerrando. Segue como foi criada a procedure no Oracle:

CREATE OR REPLACE NONEDITIONABLE PROCEDURE KILL_INACTIVE_LOCKS AUTHID DEFINER AS 
BEGIN 
  FOR r IN (
    SELECT s.sid, s.serial# 
    FROM v$session s 
    JOIN v$lock l ON s.sid = l.sid 
    WHERE s.status = 'INACTIVE' 
    AND l.block = 1 
    AND s.username NOT IN ('SYS', 'SYSTEM')
  ) 
  LOOP 
    BEGIN 
      EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION ''' || r.sid || ',' || r.serial# || ''' IMMEDIATE'; 
      DBMS_OUTPUT.PUT_LINE('Session ended: SID=' || r.sid || ', SERIAL#=' || r.serial#); 
    EXCEPTION 
      WHEN OTHERS THEN 
        DBMS_OUTPUT.PUT_LINE('Error ending session: SID=' || r.sid || ', SERIAL#=' || r.serial# || ' - ' || SQLERRM); 
    END; 
  END LOOP; 
END KILL_INACTIVE_LOCKS;

Depois criado job para ficar executando a cada 5 minutos essa procedure:

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name        => 'JOB_KILL_INACTIVE_LOCKS',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN KILL_INACTIVE_LOCKS; END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=MINUTELY;INTERVAL=5',
    enabled         => TRUE,
    comments        => 'Job to terminate inactive sessions with locks every 5 minutes.'
  );
END;
/

Aguardar o intervalo de 5 minutos e verificar se o JOB conseguiu executar sem problemas com a seguinte query:

SELECT log_id, job_name, status, actual_start_date, run_duration, error#, additional_info 
FROM dba_scheduler_job_run_details
 WHERE job_name = 'JOB_KILL_INACTIVE_LOCKS' 
ORDER BY actual_start_date DESC;