Oracle7 Server Distributed Systems Manual, Vol. 2 Go to Product Documentation Library
Library
Go to books for this product
Product
Go to Contents for this book
Contents
Go to Index
Index



Go to previous file in sequence Go to next file in sequence

Using Job Queues


This chapter describes how to use job queues to schedule periodic execution of PL/SQL code.

This chapter discusses

Note: The job queue facility is part of the standard Oracle7 Server product, and requires no special options. The symmetric replication facility makes extensive use of job queues to propagate changes between sites, and this information is provided in this document as a convenience to you.

Attention: Remember to commit you job queue changes.

Additional Information: See the Oracle7 Server Administrator's Guide for more detailed information about job queues.

Job Queues

Using the job queue, you can schedule routines to be performed periodically. A routine is any PL/SQL code.

To schedule a job, you submit it to the job queue and specify the frequency at which the job is to be run. You can also alter, disable, or delete jobs you have submitted.

SNP Background Processes

Background processes, called SNP background processes, execute job queues.

SNP processes periodically wake up and execute any queued jobs that are due to be run. You must have at least one SNP process running to execute your queued jobs in the background.

SNP background processes differ from other Oracle background processes in that the failure of an SNP process does not cause the instance to fail. If an SNP process fails, Oracle restarts it.

Multiple SNP Processes

An instance can have up to thirty-six SNP processes, named SNP0 to SNP9 and SNPA to SNPZ. If an instance has multiple SNP processes, the task of executing queued jobs can be shared across these processes, thus improving performance. Note, however, that each job is run at any point in time by only one process. A single job cannot be shared simultaneously by multiple SNP processes.

Starting Up SNP Processes

The job queue initialization parameters allow you to control the operation of the SNP background processes. Set these parameters in the initialization parameter file for an instance. They take effect the next time you start the instance.

Table 10 - 1 describes the job queue initialization parameters.

Parameter Name Description
JOB_QUEUE_PROCESSES Default: 0
Range of values: 0...36
Multiple instances: can have different values
Sets the number of SNP background processes per instance.
JOB_QUEUE_INTERVAL Default: 60 (seconds)
Range of values: 1...3600 (seconds)
Multiple instances: can have different values
Sets the interval between wake-ups for the SNP background processes of the instance.
Table 10 - 1. Job Queue Initialization Parameters

Using Job Queues

To schedule and manage jobs in the job queue, use the procedures in the DBMS_JOB package.

DBMS_JOB Package

Table 10 - 2 lists the job queue procedures in the DBMS_JOB package.

Procedure Description Discussed on
SUBMIT Submits a job to the job queue. page 10 - 5
REMOVE Removes specified job from the job queue. page 10 - 9
CHANGE Alters a specified job. You can alter the job description, the time at which the job will be run, or the interval between executions of the job. page 10 - 9
WHAT Alters the job description for a specified job. page 10 - 9
NEXT_DATE Alters the next execution time for a specified job. page 10 - 9
INTERVAL Alters the interval between executions for a specified job. page 10 - 9
BROKEN Disables or enables job execution. If a job is marked as broken, Oracle does not attempt to execute it. page 10 - 10
RUN Forces a specified job to run. page 10 - 11
Table 10 - 2. Procedures in the DBMS_JOB Package

Privileges and Job Queues

There are no database privileges associated with using job queues. Any user who can execute the job queue procedures can use the job queue.

Environment of a Job

When you submit a job to the job queue or alter a job's definition, Oracle records certain characteristics of your current environment. These characteristics include the following:

Note that the NLS_LANGUAGE and NLS_TERRITORY parameters are used as the basis for defaults of unspecified NLS parameters.

Every time a job is executed, Oracle restores the job's environment. Thus, jobs run in the same environment in which they were submitted.

A job can change its environment by using the DBMS_SQL package and the SQL command ALTER SESSION. For more information about the DBMS_SQL package, see the Oracle7 Server Application Developer's Guide. For more information about the ALTER SESSION command, see the Oracle7 Server Administrator's Guide.

Import/Export and Job Queues

Jobs can be exported and imported. Thus, if you define a job in one database, you can transfer it to another database. When exporting and importing jobs, the job's number, environment, and definition remain unchanged.

Attention: If the job number of a job you want to import matches the number of a job already existing in the database, you will not be allowed to import that job. Submit the job as a new job in the database.

Submitting a Job to the Job Queue

To submit a new job to the job queue, use the SUBMIT procedure in the DBMS_JOB package. The SUBMIT procedure returns the number of the job you submitted.

Submitting a Job: Example

Submit a new job to the job queue. The job calls the procedure DBMS_DDL.ANALYZE_OBJECT to generate optimizer statistics for the table VALERIE.ACCOUNTS. The statistics are based on a sample of half the rows of the ACCOUNTS table. The job is run every 24 hours.

SVRMGR> VARIABLE jobno number;
SVRMGR> begin
     2>           DBMS_JOB.SUBMIT(:jobno, 
     3>                   'dbms_ddl.analyze_object(''TABLE'',
     4>                   ''VALERIE'', ''ACCOUNTS'', 
     5>                   ''ESTIMATE'', NULL, 50);', 
     6>                   SYSDATE, 'SYSDATE + 1');
     7> commit;
     8> end;
     9> /
Statement processed.
SVRMGR> print jobno
JOBNO
----------
     14144

Owner of a Job

When a user submits a job to the job queue, Oracle specifies that user as the owner of the job. Only a job's owner can alter the job, force the job to run, or remove the job from the queue. Ideally, the owner of the job will be the replication administrator, however, if it is necessary that a user submit the job, the user must have the appropriate privileges at the remote site.

In a deferred transaction, note that, unless explicitly specified otherwise, the user who initiated a transaction will determine which database link is used. The owner can be specified for a deferred transaction by using the DEFER_SYS.EXECUTE parameter, execute_as_user. See page 12 - 15.

Job Numbers

A queued job is identified by its job number. When you submit a job, its job number is automatically generated from the sequence SYS.JOBSEQ.

Once a job is assigned a job number, that number does not change. Even if the job is exported and imported, its job number remains the same.

Job Definitions

The job definition is the PL/SQL code specified in the WHAT parameter of the SUBMIT procedure.

Attention: In the job definition, use two single quotation marks around strings. Always include a semicolon at the end of the job definition.

Normally, the job definition is a single call to a procedure. The procedure call can have any number of parameters.

There are special parameter values that Oracle recognizes in a job definition. Table 10 - 3 lists these parameters.

Parameter Mode Description
job IN The number of the current job.
next_date IN/OUT The date of the next execution of the job.
broken IN/OUT Status of job, broken or not broken. The IN value is FALSE.
Table 10 - 3. Special Parameter Values for Job Definitions

Job Definitions: Examples

The following examples show valid job definitions:

'myproc(''10-JAN-82'', next_date, broken);'
'scott.emppackage.give_raise(''KANE'', 3000.00);'
'dbms_job.remove(job);'

Job Execution Interval

Immediately before a job is executed, the INTERVAL date function is evaluated. If the job completes successfully, the date calculated from INTERVAL becomes the new NEXT_DATE. If the INTERVAL date function evaluates to NULL and the job completes successfully, the job is deleted from the queue.

If a job should be executed periodically at a set interval, use a date expression similar to 'SYSDATE + 7' in the INTERVAL parameter. For example, if you set the execution interval to 'SYSDATE + 7' on Monday, but for some reason (such as a network failure) the job is not executed until Thursday, 'SYSDATE + 7' now evaluates to every Thursday, not Monday.

If you always want to automatically execute a job at a specific time, regardless of the last execution (for example, every Monday), the INTERVAL and NEXT_DATE parameters should specify a date expression similar to 'NEXT_DAY(TRUNC(SYSDATE), ''MONDAY'')'.

Table 10 - 4 lists sample date expressions used for job execution intervals.

Date Expression Evaluation
'SYSDATE + 7' 7 days after the last execution
'SYSDATE + 1/48' 30 minutes after the last execution
'SYSDATE + 1/8640' 10 seconds after the last execution
'NEXT_DAY(TRUNC(SYSDATE), ''MONDAY'') + 15/24' Every Monday at 3PM
'NEXT_DAY(ADD_MONTHS (TRUNC(SYSDATE, ''Q''), 3), ''THURSDAY'')' First Thursday of each quarter
Table 10 - 4. Common Job Execution Intervals

Attention: When specifying NEXT_DATE or INTERVAL, remember that date literals and date strings must be enclosed in single quotation marks. Also, the value of INTERVAL must be enclosed in single quotes.

Additional Information: The parameters for the DBMS_JOBS.SUBMIT procedure are described in Table 12 - 36.

How Jobs Execute

SNP background processes execute jobs. To execute a job, the process creates a session to run the job.

When an SNP process runs a job, the job is run

When you force a job to run using the procedure DBMS_JOB.RUN, the job is run by your user process. When your user process runs a job, it is run with your directly granted privileges only. Privileges granted to you through roles are unavailable.

Job Queue Locks

Oracle uses job queue locks to ensure that a job is executed by only one session at a time. When a job is being run, its session acquires a job queue (JQ) lock for that job.

For more information about locking, see .

Interpreting Information about JQ Locks

The following query lists the session identifier, lock type, and lock identifiers for all sessions holding JQ locks:

SVRMGR> SELECT sid, type, id1, id2
     2>     FROM v$lock
     3>     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 lock identifier is always 0 for JQ locks. The ID2 lock identifier is the job number of the job the session is running.

For more information about the locking views, see the Oracle7 Server Reference.

Database Links and Jobs

If the job that you submit uses a database link, the link must include a username and password. Anonymous database links will not succeed.

Removing a Job From the Job Queue

To remove a job from the job queue, use the REMOVE procedure in the DBMS_JOB package.

Removing a Job: Example

Remove job number 14144 from the job queue.

DBMS_JOB.REMOVE(14144);

Restrictions

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.

Additional Information: The parameters for the procedure DBMS_JOB.REMOVE are described in Table 12 - 34.

Altering a Job

To alter a job that has been submitted to the job queue, use the procedures CHANGE, WHAT, NEXT_DATE, or INTERVAL in the DBMS_JOB package.

Restrictions

You can alter only jobs that you own. If you try to alter a job that you do not own, you receive a message that states the job is not in the job queue.

Attention: When you change a job's definition using the WHAT parameter in the procedure CHANGE or WHAT, Oracle records your current environment. This becomes the new environment for the job.

Altering a Job: Example

In the following example, the job identified by 14144 is now executed every three days. By specifying NULL for WHAT and NEXT_DATE, these values remain unchanged.

DBMS_JOB.CHANGE( job       => 14144, 
                 what      => null, 
                 next_date => null, 
                 interval  => 'SYSDATE + 3');

You could make this same change by calling the INTERVAL procedure, as shown in the following example:

DBMS_JOB.INTERVAL( job      => 14144,
                   interval => 'SYSDATE + 3');

Additional Information: The parameters for these procedures are described in the following tables:

CHANGE Table 12 - 31

WHAT Table 12 - 37

NEXT_DATE Table 12 - 33

INTERVAL Table 12 - 32

Broken Jobs

A job is labeled as either broken or not broken. Oracle does not attempt to run broken jobs. However, you can force a broken job to run by calling the procedure DBMS_JOB.RUN.

When you submit a job, it is considered not broken.

How Jobs Become Broken

There are two ways a job can break:

Marking a Job as Broken or Not Broken

To mark a job as broken or not broken, use the procedure BROKEN in the DBMS_JOB package.

Marking a Job as Not Broken: Example

The following example marks job 14144 as not broken and sets its next execution date to the following Monday:

DBMS_JOB.BROKEN( job       => 14144, 
                 broken    => FALSE, 
                 next_date => NEXT_DAY(SYSDATE, 'MONDAY'));

Once a job has been marked as broken, Oracle will not attempt to execute the job until you either mark the job as not broken, or force the job to be executed by calling the procedure DBMS_JOB.RUN.

Restrictions

You can mark only jobs you own as broken. If you try to mark a job you do not own, you receive a message that states the job is not in the job queue.

Additional Information: The parameters for DBMS_JOB.BROKEN are described in Table 12 - 30.

Forcing a Job to Be Executed

There may be times when you would like to manually execute a job. For example, if you have fixed a broken job, you may want to test the job immediately by forcing it to execute.

To force a job to be executed immediately, use the procedure RUN in the DBMS_JOB package. Oracle attempts to run the job, even if the job is marked as broken.

When you run a job using DBMS_JOB.RUN, Oracle recomputes the next execution date. For example, if you create a job on a Monday with a NEXT_DATE value of 'SYSDATE' and an INTERVAL value of 'SYSDATE + 7', the job is run every seven days starting on Monday. However, if you execute RUN on Wednesday, the next execution date will be the next Wednesday.

Forcing a Job to be Executed: Example

The following example runs job 14144 in your session and recomputes the next execution date:

DBMS_JOB.RUN( job => 14144);

Restrictions

You can run only jobs that you own. If you try to run a job that you do not own, you receive a message that states the job is not in the job queue.

The procedure RUN contains an implicit commit. Once you execute a job using RUN, you cannot rollback.

Running Broken Jobs

When you run a job that has been marked as broken and the job completes successfully, Oracle relabels the job as not broken. Oracle also resets its count of the number of failed executions for the job.

For more information about viewing the number of failures for a job and its status, see "Viewing Job Queue Information" [*].

Additional Information: The parameters for the procedure DBMS_JOB.RUN are described in Table 12 - 35.

Troubleshooting Job Execution Problems

Several factors can prevent the successful execution of queued jobs; for example:

Note: If a job fails, Oracle rolls back the job's current transaction (if any such transaction exists).

Recording Errors

When a job fails, information about the failure is recorded in a trace file and the alert log. Oracle writes message number ORA-12012 and includes the job number of the failed job.

Job Failure and Execution Times

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. When the retry interval exceeds the execution interval, Oracle continues to retry the job at the normal execution interval. However, if the job fails 16 times, Oracle automatically marks the job as broken and no longer tries to execute it.

Thus, if you can correct the problem that is preventing a job from running before the job has failed 16 times, Oracle will eventually run that job again.

Running a Job That Oracle Has Marked as Broken

If a problem has caused a job to fail 16 times, Oracle marks the job as broken. Once you have fixed this problem, you can run the job by either

If you force the job to run by calling the procedure DBMS_JOB.RUN, Oracle runs the job immediately. If the job succeeds, then Oracle labels the job as not broken and resets its count of the number of failed executions for the job.

Once you reset a job's broken flag (by calling either RUN or BROKEN), job execution resumes according to the scheduled execution intervals set for the job.

Killing a Job

You can kill a running job by marking the job as broken, identifying the session running the job, and disconnecting that session. You should mark the job as broken so that Oracle does not attempt to run the job again.

Once you have identified the session running the job, you can disconnect the session using SQL command ALTER SYSTEM.

For examples of viewing information about jobs and sessions, see "Viewing Job Queue Information" [*].

Viewing Job Queue Information

The following data dictionary views display information about jobs in the job queue:

Viewing Information about Job Status and Failed Executions

The following query lists the job number, next execution time, failures, and broken status for each job you have submitted:

SVRMGR> SELECT job, next_date, next_sec, failures, broken
     2>     FROM user_jobs;
JOB        NEXT_DATE NEXT_SEC FAILURES   B
---------- --------- -------- ---------- -
      9125 01-NOV-94 00:00:00          4 N
     14144 24-OCT-94 16:35:35          0 N
     41762 01-JAN-00 00:00:00         16 Y
3 rows selected.

Viewing Information about 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:

SVRMGR> SELECT sid, r.job, log_user, r.this_date, r.this_sec
     2>     FROM dba_jobs_running r, dba_jobs j
     3>     WHERE r.job = j.job;
SID        JOB        LOG_USER             THIS_DATE THIS_SEC
---------- ---------- -------------------- --------- --------
        12      14144 VALERIE              24-OCT-94 17:21:24
        25       8536 SCOTT                24-OCT-94 16:45:12
2 rows selected.




Go to previous file in sequence Go to next file in sequence
Prev Next
Oracle
Copyright © 1996 Oracle Corporation.
All Rights Reserved.
Go to Product Documentation Library
Library
Go to books for this product
Product
Go to Contents for this book
Contents
Go to Index
Index