Code Example. PL/SQL Kill and re-create Stuck Jobs

=====================================
General
=====================================
For a job connecting to a remote DB via a db_link, sometimes due to network issues, the job is in "stuck" mode
as a workaround, there is a job running every 2 hours, and killing jobs that run over 1 hour.


=====================================
PL/SQL CODE

=====================================

CREATE OR REPLACE PACKAGE BODY ADMIN_UTIL IS

-----------------------------------------------
-- Known Exceptions
-----------------------------------------------
  EXP_ORA_SESS_MARK_FOR_KILL            EXCEPTION;
  PRAGMA EXCEPTION_INIT(EXP_ORA_SESS_MARK_FOR_KILL,-31);
  
  -------------------------------------------------
  PROCEDURE write_sga_w_log(p_procedure_name IN VARCHAR2,
                            p_data           IN VARCHAR2) IS
  PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN
    INSERT INTO SGA_W_LOG (module_name, msg_text, msg_date)
    VALUES (p_procedure_name, p_data, SYSDATE);
    COMMIT;
  EXCEPTION
    WHEN OTHERS THEN
       NULL;
  END write_sga_w_log;
  
  
  -------------------------------------------------
  PROCEDURE KILL_LONG_RUNNING_JOB  IS
    v_module_name      SGA_W_LOG.module_name%TYPE;
    v_msg_text         SGA_W_LOG.msg_text%TYPE;
    v_sql_cmd          VARCHAR2(1000);
    
    CURSOR long_running_jobs_cur IS
    SELECT 'ALTER SYSTEM KILL SESSION ' || SUBSTR(''''||V$SESSION.sid||','||V$SESSION.serial#||'''', 1,15)||' IMMEDIATE' AS kill_cmd,
           V$SESSION.sid, 
           V$SESSION.serial#,
           DBA_JOBS.what
      FROM DBA_JOBS ,
           DBA_JOBS_RUNNING,
           V$SESSION
     WHERE DBA_JOBS.this_date < SYSDATE - 1/24
       AND DBA_JOBS_RUNNING.job = DBA_JOBS.job
       AND DBA_JOBS_RUNNING.sid = V$SESSION.sid
       AND V$SESSION.status <> 'KILLED';       
           
  BEGIN  
    v_module_name := 'KILL_LONG_RUNNING_JOB';
    
    FOR long_running_jobs_rec IN long_running_jobs_cur LOOP
      BEGIN
        v_sql_cmd := long_running_jobs_rec.kill_cmd;
        v_msg_text := 'Killing Stuck Job :'||long_running_jobs_rec.what;
        WRITE_SGA_W_LOG(v_module_name,v_msg_text);        
        
        v_msg_text := 'Runing: '||v_sql_cmd;
        WRITE_SGA_W_LOG(v_module_name,v_msg_text);
        
        EXECUTE IMMEDIATE v_sql_cmd;
    
        
      EXCEPTION
        WHEN EXP_ORA_SESS_MARK_FOR_KILL THEN
          v_msg_text := 'Done! Session Was Marked For Kill';
          WRITE_SGA_W_LOG(v_module_name,v_msg_text);          
        
        WHEN OTHERS THEN
          v_msg_text := 'Unexpected Error: '||SQLERRM;
          WRITE_SGA_W_LOG(v_module_name,v_msg_text);    
      END;
    END LOOP;    
  EXCEPTION
    WHEN OTHERS THEN
      v_msg_text := 'Unexpected Error: '||SQLERRM;
      WRITE_SGA_W_LOG(v_module_name,v_msg_text);    
  END KILL_LONG_RUNNING_JOB;    

  -------------------------------------------------
  PROCEDURE RECREATE_JOB  IS
    v_module_name      SGA_W_LOG.module_name%TYPE;
    v_msg_text         SGA_W_LOG.msg_text%TYPE;
    v_sparx_what       VARCHAR2(1000);
    CURSOR get_job_list_cur(cp_job_what IN VARCHAR2) IS
    SELECT *
      FROM DBA_JOBS 
     WHERE UPPER(WHAT) = cp_job_what;
           
  BEGIN  
    v_module_name := 'RECREATE_JOB';
    v_sparx_what := UPPER('EXT_SPARX.schedule;');
        
    
    FOR get_job_list_rec IN get_job_list_cur(v_sparx_what) LOOP

        v_msg_text := 'Deleting Job :'||get_job_list_rec.job;
        WRITE_SGA_W_LOG(v_module_name,v_msg_text);        
        
        BEGIN
          DBMS_JOB.remove(get_job_list_rec.job);
          COMMIT;
        END;
        
        v_msg_text := 'Creating Job :'||get_job_list_rec.job;
        WRITE_SGA_W_LOG(v_module_name,v_msg_text);        
        
        BEGIN
          DBMS_JOB.ISUBMIT
          ( job       => get_job_list_rec.job 
           ,what      => get_job_list_rec.what
           ,next_date => get_job_list_rec.next_date
           ,interval  => get_job_list_rec.interval
          );
          COMMIT;
        END;
    END LOOP;    
  EXCEPTION
    WHEN OTHERS THEN
      v_msg_text := 'Unexpected Error: '||SQLERRM;
      WRITE_SGA_W_LOG(v_module_name,v_msg_text);    
  END RECREATE_JOB;    
  -------------------------------------------------
END;

=====================================
Job

=====================================

DECLARE

   v_job_number NUMBER(10);
BEGIN
 DBMS_JOB.SUBMIT (JOB => v_job_number, 
                  WHAT => 'ADMIN_UTIL.KILL_LONG_RUNNING_JOB;', 
                  NEXT_DATE => TRUNC(SYSDATE,'HH24')+((FLOOR(TO_NUMBER(TO_CHAR(SYSDATE,'MI'))/1)+121)*1)/(1440), 
                  INTERVAL => 'TRUNC(SYSDATE,''HH24'')+((FLOOR(TO_NUMBER(TO_CHAR(SYSDATE,''MI''))/1)+121)*1)/(1440)'
 );
 COMMIT;
END;
/