title: Oracle procedure to kill jobs that do not complete in given time
body: <p>I have created an oracle procedure to reschedule job that do not complete in a given amount of time:</p>
  
  <pre><code>create or replace procedure kill_stuck_jobs
  as
  begin     
      for x in (  
              select j.sid,
                     s.spid,
                     s.serial#,
                     j.log_user,
                     j.job,
                     j.broken,
                     j.failures,
                     j.last_date,
                     j.this_date,
                     j.next_date,
                     j.next_date - j.last_date interval,
                     j.what
                    from 
                    (select 
                    djr.SID, 
                    dj.LOG_USER,
                    dj.JOB, 
                    dj.BROKEN,
                    dj.FAILURES, 
                    dj.LAST_DATE,
                    dj.LAST_SEC,
                    dj.THIS_DATE, dj.THIS_SEC, 
                    dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
                    from dba_jobs dj, dba_jobs_running djr
                    where dj.job = djr.job ) j,
                    (select p.spid, s.sid, s.serial#
                    from v$process p, v$session s
                    where p.addr = s.paddr ) s
                    where j.sid = s.sid and 
                    j.next_date+15/1440 &lt; sysdate  
          ) loop  
           EXEC DBMS_JOB.BROKEN(x.job,TRUE);
           execute immediate 'alter system disconnect session '''|| x.sid|| ',' || x.serial# || ''' immediate';
           EXEC DBMS_JOB.BROKEN(x.job,FALSE);
           dbms_output.put_line( 'Alter session done' );             
      end loop;  
  end;  
  </code></pre>
  
  <p>But this procedure compiles with error:</p>
  
  <pre><code>PLS-00103: Encountered the symbol "DBMS_JOB" when expecting one of the following:
  
     := . ( @ % ;
  The symbol ":=" was substituted for "DBMS_JOB" to continue.
  </code></pre>
  
  <p><img src="https://i.stack.imgur.com/vLxRm.png" alt="enter image description here"></p>
  
  <ul>
  <li><p>Here came the discussion on dbms_job and dbms_scheduler_job. Actually the problem here is I created materialized view using linked db but sometime the query is stuck for whole day in session with <code>SQL*Net more data from dblink</code> . I used the above procedure to kill jobs that are created while creating materialized view and I am killing the session using :</p>
  
  <pre><code>create or replace procedure kill_stuck_refresh as 
   begin     
       for x in (  
              select username, osuser, sid, serial#, seconds_in_wait, 
              event, state, wait_class
              from v$session
              where username is not null 
                    and seconds_in_wait &gt; 600 
                    and event = 'SQL*Net more data from dblink'  
          ) loop  
          execute immediate 'alter system disconnect session '''|| x.sid  
                       || ',' || x.serial# || ''' immediate';
          dbms_output.put_line( 'Alter session done' );             
       end loop;  
     end; -- end of kill_stuck_refresh; 
  </code></pre></li>
  </ul>