#

| Chapter 13 |

# 13. Job Scheduling in the Database

Contents:
Getting Started with DBMS_ JOB
Job Queue Architecture
Tips on Using DBMS_JOB
DBMS_JOB Examples

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.

## 13.1 Getting Started with DBMS_ JOB

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, as described in Chapter 1, Introduction) 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. Other INIT.ORA parameters that affect job queue behavior are discussed in the Section 13.2, "Job Queue Architecture"" section.

### 13.1.1 DBMS_JOB Programs

Table 13.1 lists the programs defined for the DBMS_JOB packages.

Table 13.1: DBMS_JOB Programs
Name | Description | Use in SQL?
--- | --- | ---
BROKEN | Marks the job as broken; do not re-execute | No
CHANGE | Changes job parameters that can be set by user | No
CHECK_PRIVS | Unknown | No
INTERVAL | Changes execution interval for job | No
ISUBMIT | Submits a new job specifying job number | No
NEXT_DATE | Changes next execution date for job | No
REMOVE | Removes existing job from the queue | No
RUN | Runs the job immediately in current session | No
SUBMIT | Submits a new job obtaining new job number | No
USER_EXPORT | Creates text of call to recreate a job | No
WHAT | Changes PL/SQL executed for job | No

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.

### 13.1.2 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:

Parameter | Description
--- | ---
job | Unique identifier of the job
what | PL/SQL code to execute as a job
next_date | Next execution date of the job
interval | Date function to compute next execution date of job
broken | Flags job as broken and not to be executed

The following sections describe the characteristics of these parameters that apply to all of the procedures that contain them as formal parameters.

#### 13.1.2.1 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.

#### 13.1.2.2 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. See the Section 13.2" section for more discussion of the job execution environment.

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. See the Section 13.4, "DBMS_JOB Examples"" section for an example of a job that demonstrates these powerful characteristics.

#### 13.1.2.3 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.

#### 13.1.2.4 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.

#### 13.1.2.5 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.

12.2 DBMS_SHARED_POOL: Pinning Objects |  | 13.2 Job Queue Architecture

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.