Scheduling Jobs Using Oracle’s Job Queue

OracleOracle database offers a job queue for scheduling certain operations to happen routinely in a database. The functionality is similar to UNIX cron jobs with the primary difference being if your database is not running, the job will not attempt to run.

Scheduling is accomplished through the DBMS_JOB package which is provided by Oracle. While nearly anything can be scheduled, my rule of thumb is to use this only for things which happen exclusively within the database. For tasks which involve manipulating files at the operating system level I still prefer cron.

Oracle will start a coordinator job queue (CJQ0) process to handle scheduled jobs. The CJQ0 process will keep track of the schedule and start helper processes (J000 – J999) to execute the scheduled jbos.

This is being written for use with Oracle Database 9i, but will likely work in most of the recent revisions. As always, consult the documentation for your release before attempting any of this.

Setting the database up for job execution:

Before we can schedule job execution we need to make sure the database is set up to process jobs.

The first thing we need to do is check the number of job queue processes available to execute jobs. For that we check the job_queue_processes initialization parameter.

SQL> show parameter job_queue_processes

------------------------------------ ----------- ------------------------------
job_queue_processes integer 100

This parameter should be set higher than the maximum number of simultaneous jobs you expect to run (the limit is 1000). Some jobs may require more than one process, so headroom is important.

If this parameter is set to 0 jobs will not be processed. If you wish to start processing jobs the parameter can be set dynamically with the ALTER SYSTEM command.

ALTER SYSTEM SET job_queue_processes = 100;

This command enables job queue processing by starting the CJQ0 process. Similarly, you can disable all job queue processing and stop the CJQ0 process by setting the parameter to 0.

ALTER SYSTEM SET job_queue_processes = 0;

Changes to the parameter with the ALTER SYSTEM command will only be in effect until the database is restarted. Make sure you change them in your init or spfile if you want the changes to be permanent.

Submitting a job to the queue

The procedure DBMS_JOB.SUBMIT is called to submit a new job to the queue. The procedure is called in the following format:

dbms_job.submit(JOB => :jobnumber, -- (this is a return variable, not a supplied number)
WHAT => 'code to execute',
NEXT_DATE => first_execution_date,
INTERVAL => next_execution_date);

An example should make things a little more clear:

VARIABLE jobnumber number
DBMS_JOB.SUBMIT(JOB => :jobnumber,
NEXT_DATE => to_date('11:30 01/23/06','HH24:MI MM/DD/YY'),
print jobnumber


Here we see a complete job submission. First we set up a variable to hold the job number which will be assigned automatically and returned by the procedure. We then begin a PL/SQL block and call the DBMS_JOB.SUBMIT command.

The JOB parameter will be the variable which will be populated with the job number. If you need to alter or delete a job in the future it will be easiest to find by job number.

The WHAT parameter is the code to be executed. This could be a simple statement or (as in our example) a call to another procedure or function. Note that the parameters for the procedure we’re calling must be in two single quotes so the single quotes are interpreted correctly.

The NEXT_DATE parameter specifies the first time this job should be run. This can be any valid Oracle date. Here we are telling it to run January 23 at 11:30 am.

If NEXT_DATE is not in the future you may run into problems, so it may be better to use a formula for NEXT_DATE. Anything that evaluates to a valid Oracle date is fair game.

Finally we specify INTERVAL, the way to calculate how often we want the job to run at. This is a date string which will be evaluated each time the job is run. Here SYSDATE + 1 will be evaluated to once a day. SYSDATE + .5 would cause the job to execute every 12 hours, or SYSDATE + 7 would cause it to run once a week. Any formula can be used here as long as it evaluates to a date.

The best way to assure a job will run at a specific time every day is to truncate the date and add a number of hours to it. I have explained this in greater detail in a separate article Getting a Specific Time of Day in Oracle.

If NULL is specified instead of an interval the job will be run once at the specified first execution time then be removed from the job queue.

The commit here is important to assure the newly created job will run. We then end the PL/SQL block and execute it. For future reference we print the jobnumber variable.

Now this job will be executed at 11:30am on 1/23/06 and every 24 hours after that.

Viewing the job queue

Oracle provides four useful views of the job queue.

DBA_JOBS lists information on all the jobs in the database.

ALL_JOBS has the same information as DBA_JOBS but only on jobs which are accessible tot he current user.

USER_JOBS again has the same job information, but will only list jobs owned by the current user.

DBA_JOBS_RUNNING contains information on all jobs currently running in the database. This view can be joined with the other views for detailed information on running jobs.

Running a job manually

While the point is to automate jobs, occasionally you may find you need to run a job manually. To do this you can call the RUN procedure. This is especially useful if you need to run a job which has been marked as broken.


Note: if you use DBMS_JOB.RUN to manually execute a job the value for NEXT_DATE will be updated based on the current date and time and your formula. This becomes important if you have a job running at, say 11:00 pm with an interval of SYSDATE + 1, but then you run it manually at 3:45pm, the next_date will be recalculated at that time and it will now be run at 3:45pm each day.

How to tell if a job has failed

When a database job fails an error will be written to the Oracle alert log with the error number of ORA-12012 and will include the job number which failed. Jobs could fail for a variety of reasons but most common are missing or altered objects or insufficient privileges to execute.

If a job fails to execute after 16 attempts Oracle will mark it as broken and stop attempting to run it. This will be indicated by a ‘Y’ in the BROKEN column of the dba_jobs view. If you want to tell Oracle to not run a job you can manually mark it as broken by executing the following:


The job will remain broken and will not be run until you either force it to run or mark it as not broken. When marking it as not broken you must also specify the next date for the job to run. The following will mark job 21 as not broken and have it execute at 11:00pm on January 23.

DBMS_JOB.BROKEN(JOB => 21, BROKEN => FALSE, NEXT_DATE => TO_DATE('23:00 01/23/06', 'HH24:MI MM/DD/YY'));

Changing a job

You can update a job with the DBMS_JOB.CHANGE procedure. This procedure takes job number, code, next date and interval, in that order, as conditions. Whatever you don’t want to change can be passed as NULL.

With that in mind, the command to change just the interval would look something like this:


This would change job number 21 to execute every 7 days. The previous value is discarded.

Removing a job

To remove a job from the job queue you will need the job number. If you’re not sure what it is you should be able to find it by querying one of the views listed above.

Once you have the job number, run the following command.


This will remove the job and it will not be executed again. You can only remove jobs that you own. If this is run while the job is executing it will not be interrupted, but will not be run again.

oracle, database, database administration, database development

22 thoughts on “Scheduling Jobs Using Oracle’s Job Queue”

  1. Any idea how (or is it possible) to restart a failed CJQ0 process manually without bouncing the DB (9iR2)? It failed after system suspended after archive log dest filled up and SMON doesn’t seem to want to start it up.

    Tried altering job_queue_processes to kick-start it and breaking/unbreaking jobs etc.


  2. I was nearly there – need to alter job_queue_processes to 0 first, then to a non-zero number. Then CJQ0 starts up.

  3. Maybe I’m just old school, but I still like cron better than the Oracle job schedulers. In part because I can write my scripts to gather key statistics before the job runs (load, disk utilization, etc), log it to the file, and then run sql plus logging its output to the same file. Another reason is I always seem to get burned by the DBMS_JOBS failing and then they just stop running. With cron I get all output emailed to me without any extra effort.

    I also like to email myself emails with different subjects based on the success or failure of the script. Through my shell scripts I check whether the SQL I executed succeeed or failed and then send an email with an appropriate subject. I did a little writeup on how I call PL/SQL from a BASH script at:

    Hopefully it helps you!

  4. Tim,

    I’m inclined to agree with you on using cron to schedule jobs. I only use dbms_jobs when all of the following are true:

    1) The job is completely internal to the database (does not interact with any files, doesn’t need to email, etc.)

    2) I want the job to go with if the database is duplicated or moved.

    3) The client is comfortable with maintaining dbms_jobs and has proper monitoring of the alert log set up to catch any failures.

    Otherwise, the ideal thing to do is exactly what you show on your write up. Nice job!

  5. Hi, I want to schedule a script to run every hour.
    The script is to find the no: of active users.
    a)Where will be result of the executed script present so that I can make a report of it based on time?
    b)Will the result be over written each time when the job is run?

  6. Arun,

    If your job simply executes a SELECT statement the results will not be kept anywhere. You will want the job to insert the results into a table which you can report on later.

    Consider a table named user_count with two columns, count_users of type number and count_time of type date. With that table the WHAT part of the job could read something like this: “INSERT INTO user_count (select count(*), sysdate from v$sessions);”

    Of course you could just start logging all logins with a trigger like I describe in this article.

    Hope this helps.

  7. Is there a way to query user_jobs table and get the ‘interval’ as date type?

    Assume that the only row in user_jobs has interval as TRUNC(SYSDATE+1/24,’HH24′).

    I thought something like this would do the trick:

    select (select interval from user_jobs where rownum=1) from dual;

    Ya’ll know it better than me… that won’t work. That will prompty return TRUNC(SYSDATE+1/24,’HH24′).


    select TRUNC(SYSDATE+1/24,’HH24′) from dual;

    returns what I wanted.

    We have the interval specified in bazillion different ways in our database. Some examples:

    sysdate + 1/288

    sysdate + 1/480

    TRUNC(SYSDATE+1,’DD’)+ 1/48


    sysdate + 1/48





    sysdate + 72


  8. Ashwath,

    If you’re looking for when the next execution of a given job will be you can just select the NEXT_DATE column from the user_jobs table. The INTERVAL column is stored as text (a varchar2) so will not evaluate to a date as easily as you want.

    Hope this helps.

  9. I have a question about DBMS_JOB.
    How can I put a condition inside a DBMS_JOB packge that, if condition gets true, JOB will run otherwise job will not run.

  10. Hashmi,

    I don’t think you can build a non-time based condition into the dbms_job portion, but you could certainly build your condition into a procedure and call the procedure with the scheduler.

    I think you’ll be happier with this approach anyway as it will keep the scheduled job as simple as possible.

  11. Hi Tim,

    I want to know if is possible to run a job after every database startup… I dont see how I can do this. the repeat interval of DBMS_SCHEDULER don’t allow this choice? I use Grid Control to set up my jobs.

    Thanks for the help

  12. We are submitting stored procedures thru the DBMS_JOB.SUBMIT subprogram. I would like the stored procedure submitted in this to log it’s job number (select job from user_jobs). Is there any way to access the job id from within that process?


  13. declare
    v_statement varchar2(50) NOT NULL := ‘@d:/sqlscript.sql’;
    jobno number;

    SYSDATE, ‘SYSDATE + 1’);

    Hi could someone help me, i would like to execute a script in my job queue but i keep getting error saying “encountered the symbol …”

  14. Dear all,

    can any one help as to solve this isue,

    i am using aix6.1 and database oracle 11g some time i am faceing this problem.

    Fri Jan 29 12:34:44 2010
    Process startup failed, error stack:
    Fri Jan 29 12:34:44 2010
    Errors in file /oracle/app/oracle/admin/fcctbl/bdump/fcctbl_psp0_1404974.trc:
    ORA-27300: OS system dependent operation:fork failed with status: 17
    ORA-27301: OS failure message: File exists
    ORA-27302: failure occurred at: skgpspawn5
    ORA-27303: additional information: skgpspawn5
    Fri Jan 29 12:34:45 2010
    Process J000 died, see its trace file
    Fri Jan 29 12:34:45 2010
    kkjcre1p: unable to spawn jobq slave process
    Fri Jan 29 12:34:45 2010
    Errors in file /oracle/app/oracle/admin/fcctbl/bdump/fcctbl_cjq0_1384690.trc:

  15. Somehow after updating the interval the next data is being increment little by little, is like the next run is the date of execution plus the interval.

    I can’t seem to run a job every 3 hours after 16:50, lets say:

    16:50, 19:50, 22:50 …

Leave a Reply

Your email address will not be published. Required fields are marked *