· Introduction

The DBMS_JOB package is known as the job queue.

The Oracle job queue provides programs for submitting and executing jobs, changing job execution parameters,

and removing or temporarily suspending job execution at predefined times and/or repeated job execution at regular intervals.

Users on Oracle 10g and above should use the newer DBMS_SCHEDULER package.

DBMS SCHEDULER is a more sophisticated job scheduler introduced in Oracle 10g.

The older job scheduler, DBMS_JOB, is still available.

· Data dictionary views

Data dictionary views that display information about the Oracle job queue :

DBA_JOBS DBA view describes all the jobs in the database.

ALL_JOBS ALL view describes all jobs that are accessible to the current user.

USER_JOBS USER view describes all jobs owned by the current user

DBA_JOBS_RUNNING Lists all jobs in the database that are currently running. This view can be joined with V$LOCK to identify jobs that have locks.

Displaying Information About a Job

The following query creates a listing of the job number, next execution time, failure count, and broken status for each job you have submitted:

SELECT job, next_date, next_sec, failures, broken


Displaying Information About Running Jobs

You can also display information about only the jobs currently running.

The following query lists the session identifier, job number, user who submitted the job, and the start times for all currently running jobs:

SELECT SID, r.job, log_user, r.this_date, r.this_sec


WHERE r.job = j.job;

Job Queue Locks

Oracle uses job queue locks to ensure that a job is executed in only one session at a time. When a job is being run, its session acquires a job queue (JQ) lock for that job.

You can use the locking views in the data dictionary to examine information about locks currently held by sessions.

The following query lists the session identifier, lock type, and lock identifiers for all sessions holding JQ locks:

SELECT SID, TYPE, id1, id2

FROM v$lock



——— ——— ———

12 JQ 0 14144

1 row selected.

In the query above, the identifier for the session holding the lock is 12. The ID1 column is always 0 for JQ locks. The ID2 column is the job number of the job the session is running.

This view can be joined with the DBA_JOBS_RUNNING view to obtain more information about the job.

· Initialization Parameter

An initialization parameter in the INIT.ORA file for the database. The parameter is,

1. JOB_QUEUE_PROCESSES Controls how many jobs can run, maximum number of job queue slave processes.

If this parameter is set to 0, no coordinator job queue process is started at database startup, and consequently no job queue jobs are executed.

The JOB_QUEUE_PROCESSES initialization parameter also specifies the maximum number of Jnnn processes that can concurrently run on an instance.

The maximum number of processes that can be specified is 1000.

SQL> show parameter job_queue_processes

SQL> alter system set job_queue_processes = 2 SCOPE=BOTH;

2. _JOB_QUEUE_INTERVAL This is an internal Oracle parameter. Do NOT use it unless instructed to do so by Oracle Support. Playing with this parameter may be harmful.

3. JOB_QUEUE_KEEP_CONNECTIONS This parameter became obsolete above Oracle 8.1.7.

· Procedures of the DBMS_JOB package

DBMS_JOB.SUBMIT Submits a job to the job queue

DBMS_JOB.REMOVE Removes a specified job from the job queue

DBMS_JOB.CHANGE Alters a specified job that has already been submitted to the job queue (job description, the time at which the job will be run, or the interval between executions of the job)

DBMS_JOB.WHAT Alters the job description for a specified job

DBMS_JOB.NEXT_DATE Alters the next execution time for a specified job

DBMS_JOB.INTERVAL Alters the interval between executions for a specified job

DBMS_JOB.BROKEN Sets or resets the job broken flag. If a job is marked as broken, Oracle does not attempt to execute it

DBMS_JOB.RUN Forces a specified job to run

· Submitting a Job to the Job Queue

It chooses job from the sequence sys.jobseq.





next_date IN DATE DEFAULT sysdate,

interval IN VARCHAR2 DEFAULT ‘null’,


instance IN BINARY_INTEGER DEFAULT any_instance,


Parameter Description

job Number of the job being run. The job number is automatically generated from the JOBSEQ sequence owned by user SYS.

Once a job is assigned a job number, that number does not change.

what Job defenition or PL/SQL procedure to run. The procedure call can have any number of parameters.

next_date Next date when the job will be run. The default value is SYSDATE.

interval Date function that calculates the next time to run the job. The default is NULL. This must evaluate to a either a future point in time or NULL.

no_parse A flag. The default is FALSE. If this is set to FALSE, then Oracle parses the procedure associated with the job.

If this is set to TRUE, then Oracle parses the procedure associated with the job the first time that the job is run.

For example, if you want to submit a job before you have created the tables associated with the job, then set this to TRUE.

instance When a job is submitted, specifies which instance can run the job.

force If this is TRUE, then any positive integer is acceptable as the job instance.

If this is FALSE (the default), then the specified instance must be running; otherwise the routine raises an exception.






( job => X


,next_date => TO_DATE(’01/01/4000 00:00:00′,’dd/mm/yyyy hh24:mi:ss’)

,INTERVAL => ‘SYSDATE+1440/1440’

,no_parse => TRUE



(job => X,

broken => TRUE);

SYS.DBMS_OUTPUT.PUT_LINE(‘Job Number is: ‘ || TO_CHAR(x));




The commit here is important to assure the newly created job will run

Job Definition

In the job definition, use two single quotation marks around strings. Always include a semicolon at the end of the job definition.

The following are examples of valid job definitions:

* ,what => ‘PTR.TARGET_REAL(”002”);’

* ,what => ‘get_target(”HANS”, 2007);’

Running a job from a job is not supported. You will receive an error message

“ORA-32317 cannot run a job from another job” error message in the alert file:


jobno number;


DBMS_JOB.SUBMIT(jobno, ‘DBMS_JOB.RUN(1234);’);



Job Intervals

1. Every Second.

INTERVAL => ‘SYSDATE +1/86400’;

2. Every Minute.

INTERVAL => ‘SYSDATE +1/1440’;

3. Every Ten Minute.

INTERVAL => ‘SYSDATE +10/1440’;

4. Every Half Hour.

INTERVAL => ‘SYSDATE +30/1440’;


5. Every Hour.

INTERVAL => ‘SYSDATE +60/1440’;

6. Every Day.


7. Every Day with Fixed Time. ex: every night 6:00pm.

INTERVAL => ‘trunc(SYSDATE +1) + 18/24’;

8. Every Monday at 3.00pm.


9. Every Midnight 12:00.

INTERVAL => ‘trunc(SYSDATE +1)’;

10. Every Week

INTERVAL => ‘trunc(SYSDATE +7)’;

11. Every Month

INTERVAL => ‘add_months(SYSDATE, 1)’;

12. First Thursday of each quarter


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.

· Removing a Job from the Job Queue

To remove a job from the job queue, use the REMOVE procedure in the DBMS_JOB package.

The following statements remove job number 123 from the job queue:






* You can remove currently executing jobs from the job queue. However, the job will not be interrupted, and the current execution will be completed.

* You can remove only jobs you own. If you try to remove a job that you do not own, you receive a message that states the job is not in the job queue.

Job Execution Errors

The following can cause queued jobs fail :

* a network or instance failure

* an exception when executing the job

If a job returns an error while Oracle is attempting to execute it, Oracle tries to execute it again.

The first attempt is made after one minute.

The second attempt after two minutes

The third after four minutes, and so on, with the interval doubling between each attempt.

If the job fails 16 times, Oracle automatically marks the job as broken and no longer tries to execute it.

However, between attempts, you have the opportunity to correct the problem that is preventing the job from running.

This will not disturb the retry cycle, and Oracle will eventually attempt to run the job again.

· Running a job manually

To run a job manually, you can call the RUN procedure, it is especially useful if you need to run a job which has been marked as broken.





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 08:00 pm with an interval of SYSDATE + 1, but then you run it manually at 1:23pm,

the next_date will be recalculated at that time and it will now be run at 1:23pm each day.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: