Oracle DBMS_JOB Overview

The DBMS_JOB package is actually an API into an Oracle subsystem known as the job queue. The Oracle job queue allows for the scheduling and execution of PL/SQL routines (jobs) at predefined times and/or repeated job execution at regular intervals. The DBMS_JOB package provides programs for submitting and executing jobs, changing job execution parameters, and removing or temporarily suspending job execution. This package is the only interface with the Oracle job queue.

DBMS_JOB is used to schedule many different types of tasks that can be performed in PL/SQL and that require regular execution. The job queue is used extensively by Oracle replication facilities, and was originally developed for the purpose of refreshing Oracle snapshots. DBMS_JOB is often used by DBAs to schedule regular maintenance activities on databases, typically during periods of low usage by end users. It can similarly be used by applications to schedule large batch operations during off hours. The job queue can also be used to start up service programs that listen on database pipes and respond to service requests by user sessions.

Setup

The DBMS_JOB package is created when the Oracle database is installed. The dbmsjob.sql script (found in the built-in packages source code directory) contains the source code for this package’s specification. This script is called by catproc.sql, which is normally run immediately after database creation. The script creates the public synonym DBMS_JOB for the package and grants EXECUTE privilege on the package to public. All Oracle users can reference and make use of this package.

There are several data dictionary views that display information about the Oracle job queue. These are called DBA_JOBS, USER_JOBS, and DBA_JOBS_RUNNING, and are created by the script catjobq.sql. This script is also located in the built-in packages source code directory and is automatically run by catproc.sql.

Finally, the job queue must have its dedicated background processes started in order to operate properly. This is accomplished by setting an initialization parameter in the INIT.ORA file for the database. The parameter is,

JOB_QUEUE_PROCESSES = n

where n is a number between 1 and 36.

The DBMS_JOB package does not declare any package exceptions or nonprogram elements. In addition, none of the programs in this package asserts a purity level with the RESTRICT_REFERENCES pragma.

Job Definition Parameters

The programs in DBMS_JOB share a set of parameters that define jobs, their execution times, and frequency of execution. All of the DBMS_JOB procedures manipulate one or more of these parameters. The following sections describe the characteristics of these parameters that apply to all of the procedures that contain them as formal parameters.

The job parameter

The job parameter is an integer that uniquely identifies the job. It can be either selected by the user or automatically assigned by the system, depending on which of the two job submission procedures is used to enter the job into the job queue. The DBMS_JOB.SUBMIT procedure automatically assigns the job number by obtaining the next value from the sequence SYS.JOBSEQ. It is returned as an OUT parameter so the caller can subsequently identify the job submitted. DBMS_JOB.ISUBMIT allows the user to assign a specific integer identifier to the job, and it is up to the caller to ensure that this number is unique.

Job numbers cannot be changed other than by removing and resubmitting the job. The job number is retained even when the database is exported and imported. Be aware of the potential for job number conflicts when performing export/import between databases that contain jobs.

The what parameter

The what parameter is a character string that evaluates to a valid PL/SQL call to be executed automatically by the job queue. You must enclose the what parameter in single quotes if you are using a string literal. Alternatively, you can use a VARCHAR2 variable containing the desired string value. The actual PL/SQL call must be terminated with a semicolon. To embed literal strings in the PL/SQL call, include two single quotes around the literal.

The length of the what parameter is limited to 2000 bytes under Oracle 7.3 and 4000 bytes under Oracle 8.0. These limits should be more than sufficient for all practical purposes. The value of the parameter is normally a call to a stored PL/SQL program. It is best to avoid using large anonymous PL/SQL blocks, although these are legal values. Another good tip is to always wrap stored procedure invocations in an anonymous block, as some subtle difficulties are possible otherwise. Thus, instead of,

what => 'my_proc(parm1);'

it is safer to use:

what => 'begin my_proc(parm1); end;'

Whenever the what parameter is modified to change the job to execute, the user’s current session settings are recorded and become part of the job’s execution environment. This could alter the expected execution behavior of the job if the session settings were different from those in place when the job was originally submitted. It is important to be aware of this potential side effect and be sure that session settings are correct whenever the what parameter is used in a DBMS_JOB procedure call.

Jobs that reference database links will fail if the database link is not fully qualified with the username and password. This is another subtle consequence of the execution environment of jobs.

The job definition specified by the what parameter can also reference the following "special" job parameter values:

Parameter	Mode
job	IN
next_date	IN/OUT
broken	IN/OUT

When the job definition references these job parameters in its own parameter list, their values are assigned to the parameters in the job definition when the job executes. For example, suppose that a procedure called proc1 has the following specification:

PROCEDURE proc1 (my_job_number IN INTEGER);

Suppose also that we submit proc1 to be executed by the job queue as follows:

DECLARE
   jobno   INTEGER;
BEGIN
   DBMS_JOB.SUBMIT(jobno,‘proc1(my_job_number=>job);’);
END;
/

When proc1 is executed by the queue, the my_job_number parameter is assigned the job’s job number, and thus proc1 will “know” what job number it is.

The ability to reference and modify job parameters from within the job itself enables the creation of self-modifying and self-aware jobs.

The next_date parameter

The next_date parameter tells the job queue when a job should be executed next. This parameter defaults to SYSDATE in both the DBMS_JOB.SUBMIT and BROKEN procedures, indicating that the job should be run immediately.

Whenever a NULL value is passed for the next_date parameter, the next execution date for the job is set to January 1, 4000. This effectively keeps the job from being executed without removing it from the job queue.

The next_date parameter can be set to a time in the past. Jobs are chosen for execution in order of their next execution dates, so setting a job’s next_date back can effectively move the job ahead in the queue. This can be useful in systems where the job queue processes are not keeping up with jobs to be executed, and a specific job needs to be executed as soon as possible.

The interval parameter

The interval parameter is a character string representing a valid Oracle date expression. This date expression is evaluated each time the job begins execution. When a job completes successfully, this date becomes the next execution date for the job. It is important to remember that interval evaluation and updating the job’s next execution date happen at different times. For instance, a job that takes one hour to complete and has interval set to SYSDATE+1/48 (every 30 minutes) will constantly execute, because each time it completes, it will already be 30 minutes late to execute again.

The interval expression must evaluate to either a NULL value or a time in the future. When interval evaluates to a NULL value, the job will not be re-executed after the next execution and will be automatically removed from the job queue. Thus, to execute a job one time only, pass a NULL value for the interval parameter.

Jobs may have complex execution schedules, requiring complex date arithmetic expressions for the interval parameter. The interval parameter can contain a call to a PL/SQL function with a return datatype of DATE, suggesting a nice way to encapsulate complex execution schedules within simple interval parameter values. However, experimentation with using function calls for interval resulted in erratic job execution behavior. Thus, unfortunately, a useful alternative to embedding complex date arithmetic into the interval parameter does not appear to be currently available.

The broken parameter

The broken parameter is a BOOLEAN flag used to indicate that the job is to be marked as broken (TRUE) or unbroken (FALSE). The job queue processes will not attempt to execute jobs marked as broken.

Job Queue Architecture

The job queue is really a subsystem within an Oracle database, which uses dedicated background processes and catalog tables to execute user PL/SQL procedures automatically without user intervention. It is useful to get a good conceptual understanding of the job queue, because some of the behavior of this queue is not clear. 

These three INIT.ORA parameters are instrumental in controlling the job queue:

JOB_QUEUE_PROCESSES
JOB_QUEUE_INTERVAL
JOB_QUEUE_KEEP_CONNECTIONS

 

Job Execution and the Job Execution Environment

When an SNP process wakes up, it looks in the catalog to see if the current date exceeds the next execution date for any jobs in the queue. If a job is due to execute, the SNP process will dynamically do the following:

• Become a database session with the username of the job’s owner
• Alter session NLS (National Language Support) settings to match those in place when the job was submitted or last modified
• Calculate the next execution date by applying the interval date expression to SYSDATE
• Execute the PL/SQL job definition
• If execution succeeds, upate next_date for the job with the previously calculated next execution date; otherwise, increment the number of failures
• Repeat if another job is due to run or sleep for JOB_QUEUE_INTERVAL seconds

In the first two steps, the SNP process creates a job execution environment that mimics that of a real user session that is executing the job definition’s PL/SQL. This includes setting the following NLS settings:

NLS_LANGUAGE
NLS_TERRITORY
NLS_CURRENCY
NLS_ISO_CURRENCY
NLS_NUMERIC_CHARACTERS
NLS_DATE_FORMAT
NLS_DATE_LANGUAGE
NLS_SORT

In Trusted Oracle databases, the session also sets the session label and high/low clearances.

The execution environment does not exactly mimic a user session, and this has some consequences worth noting. First, any nondefault roles that were enabled when the job was submitted will not be enabled in the job execution environment. Therefore, jobs that rely on privileges obtained through nondefault roles should not be submitted, and modification of user default roles can compromise the future execution of existing jobs. Also, any database links used in the job definition itself, or the procedures executed by it, must be fully qualified with a remote username and password. The SNP process is not able to initiate a remote session without an explicit password. Apparently, it does not assume the local user’s password as part of the execution environment session settings.

When job execution fails, the SNP processes attempts to rerun the job one minute later. If this run fails, another attempt is made in two minutes and another in four minutes. The job queue doubles the retry interval until it exceeds the normal execution interval, which is then used. After 16 consecutive failures, the job is flagged as broken and will not be re-executed by the job queue without user intervention.

 

Miscellaneous Notes

The Oracle export and import utilities preserve job numbers. Therefore, when you are importing into a database with jobs in the job queue, job number conflicts are possible. The same consideration applies when using DBMS_JOB.USER_EXPORT to transfer jobs from one database to another.

The job queue is not designed to function well under Oracle Parallel Server configurations. In particular, here are two significant limitations:

• Jobs cannot be specified to run in a specific Oracle instance.
   
• The SYS.JOB$ catalog table is not partitioned by instance and will be subject to “pinging” by job queue processes from different instances.

The workaround to these problems is to only run the job queue in a single instance of an OPS environment. This is done by setting JOB_QUEUE_PROCESSES to zero in all but one of the Oracle instances.