how to stop an Oracle dbms_job

A dbms_job is created like this:

DECLARE
  X NUMBER;
BEGIN
  DBMS_JOB.SUBMIT
  ( job       => X
   ,what      => 'My_procedure;'
   ,next_date => to_date('21/10/2010 03:00:00','dd/mm/yyyy hh24:mi:ss')
   ,interval  => 'TRUNC(sysdate)+1+3/24'
   ,no_parse  => FALSE
  );
  DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
COMMIT;
END;

This one usually runs at 3 AM, calling the procedure "My_procedure". The thing for the dbms_jobs is that you can set them to run repeatably any time you like.

But when the procedure it calls, hangs, it becomes a bit of a trick. For this example if the procedure hangs, in v$session you can see an active session running a certain sql (in most of the times it's blocking other sessions). If you try to kill the session, in a couple of minutes it appears again. This is the usual behavior of a dbms_job, if the session is killed it starts over and over again until the procedure is successfully done.

To be certain that you have any jobs running, you can run this sql:

SELECT r.sid, r.job, r.this_date, r.this_sec, what
FROM dba_jobs_running r,dba_jobs j
WHERE r.job = j.job
AND j.broken ='N';

To stop the Oracle dbms_job you must issue the following:

BEGIN
 DBMS_JOB.broken
  ( job       =>2  -- this is the job number assigned above
   ,broken  => TRUE
  );
 COMMIT;
END;

Right now you can kill the session if it's doing too much damage to the database. I recommend that you look into the procedure, or into the table of logged exceptions (maybe I'll write a post on how to log all the exceptions in a procedure).
After you are convinced that it's OK to bring the job online issue the following:

BEGIN
 DBMS_JOB.broken
  ( job       =>2
   ,broken  => FALSE
  );
 COMMIT;
END;

When you issue this command, the job starts running again. In many situations this is not a desirable thing. That's why, when I usually have a hang night job, I prefer to reschedule the job for the next day in staid of setting it to broken. This sql should do it:

BEGIN
  DBMS_JOB.CHANGE
    (
      job        => 2
     ,what       => 'My_procedure;'
     ,next_date  => to_date('22/10/2010 03:00:00','dd/mm/yyyy hh24:mi:ss')
     ,interval   => 'TRUNC(sysdate)+1+3/24'
    );
END;

Then a can kill the session, so it will not reappear until it's scheduled. Be careful how you rewrite your procedure so that the next day it wont happen again.