How to stop a running job of DBMS_JOB ?

Stop running job with DBMS_JOB

1. Find the Job You Want to stop.
---------------------------------

select j.sid,
j.log_user,
j.job,
j.broken,
j.failures,
j.last_date||':'||j.last_sec last_date,
j.this_date||':'||j.this_sec this_date,
j.next_date||':'||j.next_sec 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;


2. Mark the DBMS_JOB as Broken
------------------------------
Use the following command for the job that you have to deal with.

EXEC DBMS_JOB.BROKEN(job#,TRUE);

All this command does is mark the job so that if we get it to stop, it won't start again. Let's make
one thing perfectly clear, after executing this command the job is still running.

As a side note, if you are trying to shut down a database with jobs that run throughout the day,
they may hinder your attempts to bring down the database cleanly. This is a wonderful command
to make sure no jobs are executing during the shutdown process. Just be aware that you will need
to mark the jobs as unbroken when the database comes back up, more on that later.

3. Kill the Oracle Session
------------------------------

Since the job is still running and it isn't going to end soon, you will need to kill the Oracle
session that is executing the job. Use the following command for to kill the job.

ALTER SYSTEM KILL SESSION 'sid,serial#';

select j.sid,
s.spid,
s.serial#,
j.log_user,
j.job,
j.broken,
j.failures,
j.last_date||':'||j.last_sec last_date,
j.this_date||':'||j.this_sec this_date,
j.next_date||':'||j.next_sec 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;


4. Kill the O/S Process
--------------------------
More often than not the previous step will still leave the job attached to the database and still running.
When this happens you will need to go out to the operating system level and get rid of the process
that has spawned from the running job. In order to do this you must login to the database box and
issue the following command, depending on the type of operating system you have.

For Windows, at the DOS Prompt: orakill sid spid

For UNIX at the command line> kill '9 spid

The orakill is an Oracle command, while kill is a Unix command.

5. Check if the Job is Still Running (by the above query)
------------------------------------

6. Determine the Current Number of Job Queue Processes
------------------------------------------------------


select name,value from v$parameter where name = 'job_queue_processes';


7. Alter the Job Queue to Zero
------------------------------

ALTER SYSTEM SET job_queue_processes = 0;

This will bring down the entire job queue processes.


8. check that No Processes are Using the Job Queue (by the above query)
-----------------------------------------------------------------------


9. Mark the DBMS_JOB as Not Broken
----------------------------------

You can now reset the broken job to not broken so they can run again. Just issue the command.


SQL> EXEC DBMS_JOB.BROKEN(job#,FALSE):


10. Alter the Job Queue to Original Value
--------------------------------

Set the job queue to its' original value so that the jobs can run again.

ALTER SYSTEM SET job_queue_processes = original_value;

11. check that DBMS_JOB Is Running
---------------------------------