Bringing Down a DBMS_JOB
1. Find the Job You Want to Bring Down
In order to do anything you first
need to find the job that is giving you a headache. Go ahead and run the
running_jobs.sql. This will give you the prime information, job, sid, serial#,
and spid, for the following actions in bringing down the job.
2. Mark the DBMS_JOB as Broken
Use the following command for the
job that you have to deal with.
SQL> 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#';
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
Re-run the session_jobs.sql script
to see if you have gotten rid of the job. If you have there is no reason to go
further. Usually steps 1 through 4 will be sufficient to get rid of a job but
when the job is running wild you will have to continue with steps 6 through 11
which describes a process for bouncing the job queue process.
6. Determine the Current Number of Job Queue Processes
SQL> col value for a10
SQL> select name,value from v$parameter where name = 'job_queue_processes';
7. Alter the Job Queue to Zero
SQL> ALTER SYSTEM SET job_queue_processes = 0;
This will bring down the entire job
8. Validate that No Processes are Using the Job Queue
Re-run the session_jobs.sql script
to see if any jobs are still running. Since we have given a hard stop to the
job queue and issued the kill commands, you can now wait until no more jobs are
running. After all the jobs have quit running, you can do whatever maintenance
or tuning you need to do before proceeding.
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.
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. Validate that DBMS_JOB Is Running
To make sure everything is back to
normal, re-run the above scripts to validate that jobs are scheduled, not
broken, and are executing with the next and last dates columns changing.
Oracle have given us a great tool for scheduling activities
within the database. As with many things inside the database, not everything
goes as planned, nor are we given adequate tools to fix some of the problems we
encounter. With the eleven steps outlined here, hopefully you will have
increased your arsenal to handle those run away jobs that have given the best
of us a few tense moments.
See All Articles by Columnist James Koopmann