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
FROM DBA_JOBS_RUNNING r, DBA_JOBS j
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
WHERE TYPE = ‘JQ’;
SID TY ID1 ID2
——— — ——— ———
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.
job OUT BINARY_INTEGER,
what IN VARCHAR2,
next_date IN DATE DEFAULT sysdate,
interval IN VARCHAR2 DEFAULT ‘null’,
no_parse IN BOOLEAN DEFAULT FALSE,
instance IN BINARY_INTEGER DEFAULT any_instance,
force IN BOOLEAN DEFAULT FALSE);
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
,what => ‘MIS.CV_BILLING_POS_TEST;’
,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
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:
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’;
INTERVAL => ‘SYSDATE + 1/48’;
5. Every Hour.
INTERVAL => ‘SYSDATE +60/1440’;
6. Every Day.
INTERVAL => ‘SYSDATE +1’;
7. Every Day with Fixed Time. ex: every night 6:00pm.
INTERVAL => ‘trunc(SYSDATE +1) + 18/24’;
8. Every Monday at 3.00pm.
INTERVAL => ‘NEXT_DAY(TRUNC(SYSDATE), ”MONDAY”) + 15/24’;
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
INTERVAL => ‘NEXT_DAY(ADD_MONTHS(TRUNC(SYSDATE, ”Q”), 3), ”THURSDAY”)’;
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.
DBMS_JOB.RUN(JOB => 21);
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.