Oracle procedure to kill jobs that do not complete in given time

Question

I have created an oracle procedure to reschedule job that do not complete in a given amount of time:

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 < 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;  

But this procedure compiles with error:

PLS-00103: Encountered the symbol "DBMS_JOB" when expecting one of the following:

   := . ( @ % ;
The symbol ":=" was substituted for "DBMS_JOB" to continue.

• 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 SQL*Net more data from dblink . I used the above procedure to kill jobs that are created while creating materialized view and I am killing the session using :

  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 > 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; 
  

Answer 1

Your error stack all derives from the fact that your cursor is invalid. The cause is the ORA-00942. This can mean the table name is spelled wrong, but as you're using the data dictionary it usually points to a permissions problem, that is, the tables (or views in this case) do not exist in your scope.

Which brings us to your comment:

"I can run the select query independently I think that mean I have those priviledge. "

This suggests to me that your permissions have been granted to a role you have rights on. We can use permissions granted through roles in SQL but cannot use them to build permanent objects such as views or stored procedures. For this to work you need to have permissions on those views granted directly to your user. There is no workaround, this is the way the Oracle security model works.

---

Incidentally, you should investigate why these jobs are taking too long. I'm assuming this is a persistent problem (otherwise why are you building a stored proc to kill these jobs?). Killing jobs is a blunt instrument which wastes resources and obliterates helpful evidence. A better idea would be to find out the underlying cause of the poor performance and fix it. Perhaps there's a problem with blocking sessions and you need a locking strategy. Maybe you've got soem poorly tuned SQL. Some other job may be running at the same time and sucks up all the resources, in which case you need a decent scheduler. Or possibly you've just got more data now - the price of success - and you need to give the jobs more time to complete.