Oracle Built-in Packages

Oracle Built-in PackagesSearch this book
Previous: 13.1 Getting Started with DBMS_ JOBChapter 13
Job Scheduling in the Database
Next: 13.3 Tips on Using DBMS_JOB
 

13.2 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 obvious. Figure Figure 13.1 shows a schematic of the job queue architecture.

Figure 13.1: . Schematic of job queue architecture

Figure 13.1

13.2.1 INIT.ORA Parameters and Background Processes

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

JOB_QUEUE_PROCESSES
JOB_QUEUE_INTERVAL
JOB_QUEUE_KEEP_CONNECTIONS

13.2.1.1 JOB_QUEUE_PROCESSES

The job queue (or SNP[1]) background processes are started when the Oracle instance is started. There are as many SNP processes started as specified in the INIT.ORA parameter JOB_QUEUE_PROCESSES. The range of valid values is from 0 to 36, so there can be a maximum of 36 SNP processes per Oracle instance. Under most operating systems, the characters SNP will appear as part of the process name. For example, under UNIX, an Oracle instance called DEV with three job queue processes would show the following process names:

[1] The SNP acronym results from the fact that these special background processes were originally developed to refresh Oracle snapshots.

ora_DEV_snp0
ora_DEV_snp1
ora_DEV_snp2

One significant difference between the SNP background processes and other Oracle background processes is that killing an SNP process will not crash the instance. While you're not likely to want to do this very often, this behavior is useful to know in case a job queue process "runs away" and consumes excessive resources. When an SNP process is killed or fails on its own, Oracle automatically starts a new one to replace it.

13.2.1.2 JOB_QUEUE_INTERVAL

The job queue processes "wake up" periodically and check the job queue catalog to see if any jobs are due to execute. The INIT.ORA parameter JOB_QUEUE_INTERVAL controls how long the SNP processes "sleep" (in seconds) between catalog checks. Setting the interval too low can cause unnecessary overhead as SNP processes constantly check the catalog. Setting the interval too high can keep jobs from executing at the expected time if an SNP process does not awaken promptly enough. The proper balance will depend on the specific mix of jobs in a given environment. For most purposes, the default setting of 60 seconds is adequate.

13.2.1.3 JOB_QUEUE_KEEP_CONNECTIONS

The third INIT.ORA parameter that supposedly affects the behavior of the SNP processes is JOB_QUEUE_KEEP_CONNECTIONS. This parameter has been made obsolete in Oracle8i. In fact, it apparently never actually had any effect under previous releases, although it has been documented as having various effects. Some sources say that it controlled the database sessions held by the SNP background processes; others say that it controlled the sessions in remote databases for jobs using database links.

Although setting this parameter appears to do no harm, it is best to leave it alone.

13.2.2 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:

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.

13.2.3 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:

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.

13.2.4 DBMS_JOB Interface

This section describes the programs available in the DBMS_JOB package.

13.2.5 Submitting Jobs to the Job Queue

Use the SUBMIT and ISUBMIT procedures to submit jobs to the job queue.

13.2.5.1 The DBMS_JOB.SUBMIT procedure

The SUBMIT procedure submits a new job to the job queue. The job number is obtained from the sequence SYS.JOBSEQ and returned as an OUT parameter. Here's the header for this program:

PROCEDURE DBMS_JOB.SUBMIT
   (job OUT BINARY_INTEGER
   ,what IN VARCHAR2
   ,next_date IN DATE DEFAULT SYSDATE
   ,interval IN VARCHAR2 DEFAULT 'null'
   ,no_parse IN BOOLEAN DEFAULT FALSE);

Parameters are summarized in the following table.

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 expression to compute next execution date of job

no_parse

Flag indicating whether to parse job PL/SQL at time of submission (FALSE) or execution (TRUE)

13.2.5.1.1 Exceptions.

The program does not raise any package exceptions. The interval date expression must evaluate to a future date or the following Oracle exception will be raised:

ORA-23420

Interval must evaluate to a time in the future.

13.2.5.1.2 Example.

This SQL*Plus script submits a job that uses DBMS_DDL.ANALYZE_OBJECT to analyze a particular table every day at midnight:

var jobno NUMBER

BEGIN
   DBMS_JOB.SUBMIT
      (job => :jobno
      ,what => 'DBMS_DDL.ANALYZE_OBJECT(''TABLE'',''LOAD1'',''TENK'',
                   ''ESTIMATE'',null,estimate_percent=>50);'
      ,next_date => TRUNC(SYSDATE+1)
      ,interval => 'TRUNC(SYSDATE+1)'
      );
END;
/
print jobno

The what parameter must be enclosed in single quotes and the PL/SQL call terminated with a semicolon. To embed literal strings in the PL/SQL call, use two single quotes around the literal.

The no_parse parameter controls when the job's PL/SQL definition is actually parsed. The default value of FALSE specifies that the PL/SQL is parsed immediately when the job is submitted. Alternatively, if you specify TRUE, parsing can be deferred until the first execution of the job. This allows jobs to be submitted into the queue for future execution where objects necessary for execution (tables, packages, etc.) are not in place at the time of submission.

TIP: On some platforms and versions of Oracle, a COMMIT is required for the job to be picked up by the job queue for execution. If submitted jobs do not seem to be executing at all, this may be the cause. The workaround for this problem is to always COMMIT immediately after calling SUBMIT.

To execute a job one time only, pass a NULL value for the interval parameter.

13.2.5.2 The DBMS_JOB.ISUBMIT procedure

The ISUBMIT procedure submits a new job to the job queue with the specified job number. Here's the header for this program:

PROCEDURE DBMS_JOB.ISUBMIT
   (job IN BINARY_INTEGER
   ,what IN VARCHAR2
   ,next_date IN VARCHAR2
   ,interval IN VARCHAR2 DEFAULT 'null'
   ,no_parse IN BOOLEAN DEFAULT FALSE);

Parameters are summarized in the following table.

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 expression to compute next execution date of job

no_parse

Flag indicating whether to parse job PL/SQL at time of submission (FALSE) or execution (TRUE)

13.2.5.2.1 Exceptions

The program does not raise any packaged exceptions. The interval date expression must evaluate to a future date or the following Oracle exception will be raised:

ORA-23420

Interval must evaluate to a time in the future.

The catalog table that records job queue entries is protected by a unique constraint on the job number. Therefore, using the ISUBMIT procedure to submit a job number that already exists results in the following error:

ORA-00001

Unique constraint (SYS.I_JOB_JOB) violated.

13.2.5.2.2 Example

The following example submits three jobs to the job queue, numbered 1, 2, and 3. Job 1 passes a string and number into procedure my_job1, runs it in one hour, and executes it every day thereafter. Job 2 passes a date into procedure my_job2, executes for the first time tomorrow, and execute it every 10 minutes thereafter. Job 3 is a PL/SQL block that does nothing, executes immediately, and will be removed from the queue automatically.

BEGIN
   DBMS_JOB.ISUBMIT
      (job  => 1
      ,what => 'my_job1(''string_parm_value'',120);'
      ,next_date => SYSDATE + 1/24
      ,interval => 'SYSDATE +1');

   DBMS_JOB.ISUBMIT
      (2, 'my_job2(date_IN=>SYSDATE);'
        ,SYSDATE+1,'SYSDATE+10/1440');

   DBMS_JOB.ISUBMIT(3,'BEGIN null; END;',SYSDATE,null);
END;

The ISUBMIT procedure allows the calling user or application to decide the job identification number. Collisions in job numbers will result in the unique constraint violation noted earlier. Therefore, it is probably better not to embed fixed job numbers into applications (as this will increase the chances for collisions) and to use SUBMIT instead of ISUBMIT. If specific job numbering is required, then you can minimize job number collisions by pushing the SYS.JOBSEQ sequence out to a number greater than those used in calls to ISUBMIT. This can be accomplished by consuming sequence numbers as follows:

SELECT SYS.JOBSEQ.NEXTVAL
  FROM dictionary
 WHERE rownum < 101;

After issuing the previous command, DBMS_JOB.SUBMIT will always return job numbers higher than 100. Note that in this command, the dictionary view is not special, but is used because it is publicly accessible and contains more than 100 rows. You can substitute any table or view accessible to the user.

WARNING: When you are using the ISUBMIT procedure, a subtle problem can arise from the fact that next_date has datatype VARCHAR2 instead of DATE (as in SUBMIT). When DATE values are assigned to next_date in calls to ISUBMIT, an implicit conversion to VARCHAR2 is done using the session's current NLS_DATE_FORMAT as the date mask. This can result in unexpected truncation of next_date. For instance, if the session NLS_DATE_FORMAT is `YY-MM-DD', dates assigned to next_date will be truncated to 12:00 a.m. To avoid this problem, either make sure that the session NLS_DATE_FORMAT includes all date components or supply next_date as a fully specified VARCHAR2.

13.2.6 Modifying Job Characteristics

This section describes the procedures you use to modify job characteristics: CHANGE, INTERVAL, NEXT_DATE, and WHAT.

13.2.6.1 The DBMS_JOB.CHANGE procedure

The CHANGE procedure alters one or more of the user-definable parameters of a job. When a null value is passed for any of these parameters (what, next_date, or interval) the current setting is not modified. Here's the header for this program:

PROCEDURE DBMS_JOB.CHANGE
   (job IN BINARY_INTEGER
   ,what IN VARCHAR2
   ,next_date IN DATE
   ,interval IN VARCHAR2);

Parameters are summarized in the following table.

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 expression to compute next execution date of job

13.2.6.1.1 Exceptions

The program does not raise any packaged exceptions. The interval date function must evaluate to a future date or the following Oracle exception will be raised:

ORA-23420

Interval must evaluate to a time in the future.

13.2.6.1.2 Restrictions

The CHANGE procedure can be executed only for jobs owned by the username to which the session is connected. These jobs are visible in the dictionary view USER_JOBS. The USER_JOBS dictionary view is discussed in the Section 13.3, "Tips on Using DBMS_JOB"" section.

13.2.6.1.3 Example

The execution schedule of job 100 can be changed to next execute tomorrow at 6:00 a.m. and every two hours after that, as follows:

BEGIN
   DBMS_JOB.CHANGE(100,null,TRUNC(SYSDATE+1)+6/24,'SYSDATE+2/24');
END;
/

When the what parameter is changed to modify the actual job to execute, the user's current session NLS settings are also recorded and become part of the job's execution environment.

13.2.6.2 The DBMS_JOB.INTERVAL procedure

The INTERVAL procedure changes the date expression, which is used to determine the next execution date of a job. Here's the header for this program:

PROCEDURE DBMS_JOB.INTERVAL
   (job IN BINARY_INTEGER
   ,interval IN VARCHAR2);

Parameters are summarized in the following table.

Parameter

Description

job

Unique identifier of the job

interval

Date expression to compute next execution date of job

13.2.6.2.1 Exceptions

The program does not raise any package exceptions. The interval date expression must evaluate to a future date or the following Oracle exception will be raised:

ORA-23420

Interval must evaluate to a time in the future.

13.2.6.2.2 Restrictions

The INTERVAL procedure can be executed only for jobs owned by the username to which the session is connected. These jobs are visible in the dictionary view USER_JOBS. The USER_JOBS dictionary view is discussed in the Section 13.3" section.

13.2.6.2.3 Example

The following SQL*Plus command will modify job 100 to execute every day at 6:00 a.m.:

SQL> execute DBMS_JOB.INTERVAL(100, 'TRUNC(SYSDATE+1)+6/24');

The date expression must be specified as a string literal or a VARCHAR2 variable containing a string literal. Literals that evaluate to PL/SQL functions are accepted by DBMS_JOB, but have been observed to cause erratic job execution behavior.

A job can be removed automatically from the job queue after its next execution by passing NULL for the interval parameter.

13.2.6.3 The DBMS_JOB.NEXT_DATE procedure

The NEXT_DATE procedure changes the job's next scheduled date of execution. Here's the header for this program:

PROCEDURE DBMS_JOB.NEXT_DATE
   (job IN BINARY_INTEGER
   ,next_date IN DATE);

Parameters are summarized in the following table.

Parameter

Description

job

Unique identifier of the job

next_date

Next execution date of the job

The program does not raise any named exceptions.

13.2.6.3.1 Restrictions

The NEXT_DATE procedure can be executed only for jobs owned by the username to which the session is connected. These jobs are visible in the dictionary view USER_JOBS. The USER_JOBS dictionary view is discussed in the Section 13.3" section.

13.2.6.3.2 Example

This example shows a SQL*Plus example of how to schedule the next execution of job 100 for next Monday:

SQL> execute DBMS_JOB.NEXT_DATE(100, NEXT_DAY(SYSDATE,'MONDAY'));

When 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.

13.2.6.4 The DBMS_JOB.WHAT procedure

The WHAT procedure changes the PL/SQL call that comprises the job's PL/SQL definition. Here's the header for this program:

PROCEDURE DBMS_JOB.WHAT
   (job IN BINARY_INTEGER
   ,what IN VARCHAR2);

Parameters are summarized in the following table.

Parameter

Description

job

Unique identifier of the job

what

PL/SQL code to execute as a job

13.2.6.4.1 Restrictions

The WHAT procedure can be executed only for jobs owned by the username to which the session is connected. These jobs are visible in the dictionary view USER_JOBS. The USER_JOBS dictionary view is discussed in the Section 13.3" section.

13.2.6.4.2 Example

In this example, job 100 is modified to execute a procedure called my_package.proc1. When the job is run by the job queue, it will run in a session that has NLS_DATE_FORMAT set as in the ALTER SESSION command.

SQL> ALTER SESSION SET NLS_DATE_FORMAT='YYYY:MM:DD:HH24:MI:SS';

SQL> execute dbms_job.what(100,'my_package.proc1;');

When the what parameter is changed to modify the actual job to execute, the user's current session NLS settings are also recorded and become part of the job's execution environment.

The what parameter must be enclosed in single quotes and the PL/SQL call must be terminated with a semicolon. To embed literal strings in the PL/SQL call, use two single quotes around the literal.

13.2.7 Removing Jobs and Changing Job Execution Status

The REMOVE, BROKEN, and RUN procedures let you remove jobs from the job queue and change the execution status of jobs.

13.2.7.1 The DBMS_JOB.REMOVE procedure

The REMOVE procedure removes an existing job from the job queue. If the job is currently executing, it will run to normal completion, but will not be rescheduled. The header for this procedure is:

PROCEDURE DBMS_JOB.REMOVE
   (job IN BINARY_INTEGER);

where job is the unique identifier of the job. This program does not raise any package exceptions.

13.2.7.1.1 Restrictions

The REMOVE procedure can be executed only for jobs owned by the username to which the session is connected. These jobs are visible in the dictionary view USER_JOBS. The USER_JOBS dictionary view is discussed in the Section 13.3" section.

13.2.7.1.2 Example

To remove job number 100 from the job queue in SQL*Plus, specify the following:

SQL> execute DBMS_JOB.REMOVE(100);

When REMOVE is executed for a job that is currently executing, the job is removed from the job queue, but the current execution is allowed to complete. Terminating a running job and removing it from the job queue is described in the Section 13.3" section later in this chapter.

13.2.7.2 The DBMS_JOB.BROKEN procedure

The BROKEN procedure is used to set or unset the broken flag for a job. Jobs flagged as broken are not automatically re-executed. Here's the header for this program:

PROCEDURE DBMS_JOB.BROKEN
   (job IN BINARY_INTEGER
   ,broken IN BOOLEAN
   ,next_date IN DATE DEFAULT SYSDATE);

Parameters are summarized in the following table.

Parameter

Description

job

Unique identifier of the job

broken

Flag indicating job is broken (TRUE) or not broken (FALSE)

next_date

Next execution date of the job

The program does not raise any package exceptions.

13.2.7.2.1 Restrictions

The BROKEN procedure can be executed only for jobs owned by the username to which the session is connected. These jobs are visible in the dictionary view USER_JOBS. The USER_JOBS dictionary view is discussed in the Section 13.3" section.

13.2.7.2.2 Example

All jobs owned by the current user are set to broken by this PL/SQL block:

BEGIN
   FOR job_rec IN (SELECT job FROM user_jobs) 
   LOOP
      DBMS_JOB.BROKEN(job_rec.job,TRUE);
   END LOOP;
END;
/

Jobs are marked as broken by passing TRUE for the broken parameter. In this case, the next execution for the job date is automatically set to January 1, 4000, regardless of the value of the next_date parameter passed. Although it looks strange, this is not a problem and is merely another safeguard preventing the job queue processes from executing broken jobs.

When marking jobs as not broken by passing the value FALSE for the broken parameter, the value of next_date becomes the next execution date for the job. Since next_date has a default value of SYSDATE, marking a job as unbroken without specifying next_date explicitly indicates that the job should execute immediately. Be careful to pass an explicit value for next_date if immediate execution is not the desired behavior. Note also that DBMS_JOB.BROKEN (job,FALSE) will always modify the next execution date of the job, regardless of whether it was marked broken.

13.2.7.3 The DBMS_JOB.RUN procedure

The RUN procedure immediately executes the job in the current session. The header for this program follows:

PROCEDURE DBMS_JOB.RUN
   (job IN BINARY_INTEGER);

The job parameter is the unique identifier for the job. The program does not raise any package exceptions.

13.2.7.3.1 Restrictions

The RUN procedure can be executed only for jobs owned by the username to which the session is connected. These jobs are visible in the dictionary view USER_JOBS. The USER_JOBS dictionary view is discussed in the Section 13.3" section.

13.2.7.3.2 Example

To run job number 100 immediately in the current session, specify the following:

SQL> execute DBMS_JOB.RUN(100);

The RUN procedure performs an implicit COMMIT in the current session. It runs the job with the current session's settings and privileges as the execution environment. Be aware that these could be different from the execution environment settings specified for the job and used by the job queue when it runs the job. This could cause unexpected results, so it is best to execute RUN from a session with the same environment as the job.

Also, issuing the RUN procedure computes the next execution date for the job using the current SYSDATE as the seed value. This could throw off the execution schedule of some jobs, depending on how the interval is defined. See Section 13.3" for a discussion of job intervals and date arithmetic.

13.2.8 Transferring Jobs

The USER_EXPORT procedure lets you export jobs in the job queue to a file for re-creation or transfer to another database.

13.2.8.1 The DBMS_JOB.USER_EXPORT procedure

The USER_EXPORT procedure produces a character string that can be used to re-create an existing job in the job queue. The string contains a call to the ISUBMIT procedure for the job, which specifies the current values for the job definition parameters. Here's the header for the program:

PROCEDURE DBMS_JOB.USER_EXPORT
   (job IN BINARY_INTEGER
   ,mycall IN OUT VARCHAR2);

Parameters are summarized in the following table.

Parameter

Description

job

Unique identifier of the job

mycall

String containing call to the ISUBMIT procedure to re-create job

The program does not raise any package exceptions.

13.2.8.1.1 Example.

This SQL*Plus script shows that current settings for the job definition parameters are placed into the mycall parameter of USER_EXPORT:

/* Filename on companion disk: job1.sql */*
set array 1
var job number
var jobstring VARCHAR2(2000)
col jobstring format a50 word_wrap
col what format a25 word_wrap
col interval format a20

ALTER SESSION SET NLS_DATE_FORMAT='YYYY:MM:DD:HH24:MI:SS';

BEGIN
   /* submit no-op job to execute every 30 seconds */
   DBMS_JOB.SUBMIT(:job,'begin null;end;',SYSDATE,'SYSDATE+1/2880');

   /* commit to make sure the submit "takes" */
   COMMIT;
   /* sleep for two minutes to let job execute a few times */
   DBMS_LOCK.SLEEP(120);
END;
/

SELECT job,what,next_date,interval
  FROM dba_jobs
 WHERE job = :job;

BEGIN
   /* export the job */
   DBMS_JOB.USER_EXPORT(:job,:jobstring);
END;
/

print jobstring

The following output was generated by the script. Notice that the current value of NEXT_DATE (as shown by querying DBA_JOBS) is extracted and placed into the string value returned in the mycall parameter as the value for next_date in the call to ISUBMIT.

Session altered.


PL/SQL procedure successfully completed.


   JOB WHAT                      NEXT_DATE           INTERVAL
------ ------------------------- ------------------- --------------
   175 begin null;end;           1997:11:16:16:22:59 SYSDATE+1/2880


PL/SQL procedure successfully completed.


JOBSTRING
-------------------------------------------------- 
dbms_job.isubmit(job=>175,what=>'begin
null;end;',next_date=>to_date('1997-11-16:16:22:59
','YYYY-MM-DD:HH24:MI:SS'),interval=>'SYSDATE+1/28
80',no_parse=>TRUE);


Previous: 13.1 Getting Started with DBMS_ JOBOracle Built-in PackagesNext: 13.3 Tips on Using DBMS_JOB
13.1 Getting Started with DBMS_ JOBBook Index13.3 Tips on Using DBMS_JOB

The Oracle Library Navigation

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

Library Home Oracle PL/SQL Programming, 2nd. Ed. Guide to Oracle 8i Features Oracle Built-in Packages Advanced PL/SQL Programming with Packages Oracle Web Applications Oracle PL/SQL Language Pocket Reference Oracle PL/SQL Built-ins Pocket Reference