Day 19

Managing Database Jobs

by Timothy Atwood

As with any production system, Oracle provides a way to handle jobs with the DBMS_JOB package. This package enables you to schedule jobs immediately, or schedule jobs at a precise time and day, which could be monthly, weekly, and so on. This lesson also discusses how to communicate between multiple processes. Today's lesson focuses on

• The DBMS_JOB package
• Running a job in the background
• Running a job immediately
• Viewing jobs
• Removing a job
• Altering a job

• Importing and exporting jobs
• Working with broken jobs
• Using pipes
• Public versus private pipes
• Using pipe messages

The DBMS_JOB Package

The DBMS_JOB package enables you to submit jobs to a job queue. From the job queue, you can schedule these jobs to execute immediately, or you can specify when to run the jobs and how often to run the jobs. In addition, you can find information on currently executing jobs, broken jobs, the scheduling of jobs, and other pieces of job information. The DBMS_JOB package requires PL/SQL 2.2 or higher. You also need the appropriate privileges to access the DBMS_JOB package.

Background Processes

Instead of utilizing a lot of resources by running the same multiple programs for each user to schedule or run a job, Oracle provides SNP background processes. These processes share common functions and code, which allows them to monitor Oracle processes for possible parallel execution. One key feature of SNP (Snapshot Refresh Process) is that if a job fails, it does not bring down the database as other processes would. In addition, SNP processes monitor jobs at user-specified time intervals, start any jobs that need to be executed, and then wait for the next time interval. You could liken this to grocers monitoring for expired food in the freezer or refrigerator. At night after close (specific time interval), the background process looks for jobs to execute (employees scan for expired food), and if found (expired food found), the job executes (food is discarded), and the process waits to repeat the process again (employees go home and will repeat the same process tomorrow).

Oracle provides up to 10 SNP processes identified as SNP0 through SNP9. The three parameters defined in the INIT.ORA file are listed in Table 19.1.

Table 19.1. SNP parameters.

Each job uses one process; therefore, you cannot have one job executing across multiple processes.

Execution of Jobs

There are two methods of job execution—timed by submitting to a job queue, or immediate execution. This section focuses first on submitting jobs on a timed basis to a job queue through the use of the SUBMIT procedure or the ISUBMIT procedure.

Submitting Jobs to the Job Queue with SUBMIT

SUBMIT is used to submit jobs to the job queue.

The Syntax for the SUBMIT Procedure
The format for SUBMIT is

PROCEDURE SUBMIT(job_number OUT BINARY_INTEGER,
     job_to_submit IN VARCHAR2,
     next_run IN DATE DEFAULT SYSDATE,
     interval IN VARCHAR2 DEFAULT NULL,
     job_parsing IN BOOLEAN DEFAULT false);

You can review the parameters for SUBMIT in Table 19.2.

Table 19.2. Parameters for SUBMIT.

Examples of Using SUBMIT
Take a look at some sample listings for submitting jobs. The first listing, Listing 19.1, is a basic submittal of a procedure HELLO. This procedure is a stored procedure, which does not include any arguments.

Listing 19.1. A simple procedure with SUBMIT.

1: DECLARE
2:      v_JobNum  BINARY_INTEGER;
3: BEGIN
4:      DBMS_JOB.SUBMIT(v_JobNum,'HELLO;',SYSDATE,
5:           `SYSDATE + (1/(24*60*60))');
6: END;

ANALYSIS First, the program declares a variable of type BINARY_INTEGER in line 2 to hold the value of the job number assigned to the job. Then it submits the job in line 4 by passing the job number assigned, the PL/SQL code to execute (HELLO), the next date to execute (system date), and the interval (system date executing every minute). You can compute this by multiplying 24 hours in the day by 60 minutes/hour by 60 seconds/minute and then taking the inverse.