Oracle7 Server Administrator's Guide

Managing Job Queues

This section describes the various aspects of managing job queues, and includes the following topics:

DBMS_JOB Package

Submitting a Job to the Job Queue

How Jobs Execute

Removing a Job From the Job Queue

Altering a Job

Broken Jobs

Forcing a Job to Execute

Terminating a Job

DBMS_JOB Package

To schedule and manage jobs in the job queue, use the procedures in the DBMS_JOB package. There are no database privileges associated with using job queues. Any user who can execute the job queue procedures can use the job queue. Table 7 - 2 lists the job queue procedures in the DBMS_JOB package.

Procedure Description Described
SUBMIT Submits a job to the job queue.
REMOVE Removes specified job from the job queue.
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.
WHAT Alters the job description for a specified job.
NEXT_DATE Alters the next execution time for a specified job.
INTERVAL Alters the interval between executions for a specified job.
BROKEN Disables job execution. If a job is marked as broken, Oracle7 does not attempt to execute it.
RUN Forces a specified job to run.

Table 7 - 2. Procedures in the DBMS_JOB Package

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:

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)

The SUBMIT procedure returns the number of the job you submitted. Table 7 - 3 describes the procedure's parameters.

Parameter Description
job This is the identifier assigned to the job you created. You must use the job number whenever you want to alter or remove the job.
For more information about job numbers, see "Job Numbers" .
what This is the PL/SQL code you want to have executed.
For more information about defining a job, see "Job Definitions" .
next_date This is the next date when the job will be run. The default value is SYSDATE.
interval This is the date function that calculates the next time to execute the job. The default value is NULL. INTERVAL must evaluate to a future point in time or NULL.
For more information on how to specify an execution interval, see .
no_parse This is a flag. The default value is FALSE.
If NO_PARSE is set to FALSE (the default), Oracle7 parses the procedure associated with the job. If NO_PARSE is set to TRUE, Oracle7 parses the procedure associated with the job the first time that the job is executed. If, for example, you want to submit a job before you have created the tables associated with the job, set NO_PARSE to TRUE.

Table 7 - 3. Parameters for DBMS_JOB.SUBMIT

As an example, let's submit a new job to the job queue. The job calls the procedure DBMS_DDL.ANALYZE_OBJECT to generate optimizer statistics for the table DQUON.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> 			''DQUON'', ''ACCOUNTS'', 
     5> 			''ESTIMATE'', NULL, 50);' 
     6> 			SYSDATE, 'SYSDATE + 1');
     7> end;
     8> /
Statement processed.

SVRMGR> print jobno
JOBNO
----------
     14144

Job Environment

When you submit a job to the job queue or alter a job's definition, Oracle7 records the following environment characteristics:

the current user

the user submitting or altering a job

the current schema

MAC privileges (if appropriate)

Oracle also records the following NLS parameters:

NLS_LANGUAGE

NLS_TERRITORY

NLS_CURRENCY

NLS_ISO_CURRENCY

NLS_NUMERIC_CHARACTERS

NLS_DATE_FORMAT

NLS_DATE_LANGUAGE

NLS_SORT

Oracle restores these environment characteristics every time a job is executed. NLS_LANGUAGE and NLS_TERRITORY parameters are defaults for unspecified NLS parameters.

You can change a job's environment by using the DBMS_SQL package and the ALTER SESSION command.

Jobs and Import/Export

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.

Note: 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.

Job Owners

When you submit a job to the job queue, Oracle7 identifies you 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.

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.

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

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

There are special parameter values that Oracle7 recognizes in a job definition. Table 7 - 4 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. The default value is SYSDATE.
broken IN/OUT Status of job, broken or not broken. The IN value is FALSE.

Table 7 - 4. Special Parameter Values for Job Definitions

The following are examples of valid job definitions:

'myproc(''10-JAN-82'', next_date, broken);'

'scott.emppackage.give_raise(''JFEE'', 3000.00);'

'dbms_job.remove(job);'

Job Execution Interval

The INTERVAL date function is evaluated immediately before a job is executed. 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' then executes 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 7 - 5 lists some common date expressions used for job execution intervals.

Date Expression Evaluation
'SYSDATE + 7' exactly seven days from the last execution
'SYSDATE + 1/48' every half hour
'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 7 - 5. Common Job Execution Intervals

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

Database Links and Jobs

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

See Also: For more information about the ALTER SESSION command, see Oracle7 Server SQL Reference.

For more information on the DBMS_SQL package, see the Oracle7 Server Application Developer's Guide.

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 in the same environment in which it was submitted and with the owner's default privileges.

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 default privileges only. Privileges granted to you through roles are unavailable.

Job Queue Locks

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

Interpreting Information about JQ Locks You can use the Server Manager Lock Monitor or the locking views in the data dictionary to examine information about locks currently held by sessions. 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.

Job Execution Errors

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

The following can prevent the successful execution of queued jobs:

not having any SNP background processes to run the job

a network or instance failure

an exception when executing the job

Job Failure and Execution Times If a job returns an error while Oracle7 is attempting to execute it, Oracle7 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, Oracle7 continues to retry the job at the normal execution interval. However, if the job fails sixteen times, Oracle7 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 sixteen times, Oracle7 will eventually run that job again.

See Also: For more information about the locking views, see the .

For more information about locking, see .

Removing a Job From the Job Queue

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

DBMS_JOB.REMOVE(job IN BINARY_INTEGER)

The following statement removes 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 only remove 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.

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.

Here's an example where the job identified as 14144 is now executed every three days:

DBMS_JOB.CHANGE(14144, null, null, 'SYSDATE + 3');

Restrictions

You can only alter 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.

Syntax for CHANGE

You can alter any of the user-definable parameters associated with a job by calling the DBMS_JOB.CHANGE procedure. Table 7 - 3 describes the procedure's parameters:

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

If you specify NULL for WHAT, NEXT_DATE, or INTERVAL when you call the procedure CHANGE, the current value remains unchanged.

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

Syntax for WHAT

You can alter the definition of a job by calling the DBMS_JOB.WHAT procedure. Table 7 - 3 describes the procedure's parameters:

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

Note: When you execute procedure WHAT, Oracle7 records your current environment. This becomes the new environment for the job.

Syntax for NEXT_DATE

You can alter the next date that Oracle7 executes a job by calling the DBMS_JOB.NEXT_DATE procedure. Table 7 - 3 describes the procedure's parameters:

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

Syntax for INTERVAL

You can alter the execution interval of a job by calling the DBMS_JOB.INTERVAL procedure. Table 7 - 3 describes the procedure's parameters:

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

Broken Jobs

A job is labeled as either broken or not broken. Oracle7 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.

There are two ways a job can break:

Oracle7 has failed to successfully execute the job after sixteen attempts.

You have marked the job as broken, using the procedure DBMS_JOB.BROKEN.

To mark a job as broken or not broken, use the procedure BROKEN in the DBMS_JOB package. Table 7 - 4 describes the procedure's parameters:

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

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

DBMS_JOB.BROKEN(14144, FALSE, NEXT_DAY(SYSDATE, 'MONDAY'));

Once a job has been marked as broken, Oracle7 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 only mark 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.

Running Broken Jobs

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

forcing the job to run by calling DBMS_JOB.RUN

marking the job as not broken by calling DBMS_JOB.BROKEN and waiting for Oracle7 to execute the job

If you force the job to run by calling the procedure DBMS_JOB.RUN, Oracle7 runs the job immediately. If the job succeeds, then Oracle7 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.

Forcing a Job to Execute

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. Oracle7 attempts to run the job, even if the job is marked as broken:

DBMS_JOB.RUN(	job	IN	BINARY_INTEGER)

When you run a job using DBMS_JOB.RUN, Oracle7 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 7 days starting on Monday. However, if you execute RUN on Wednesday, the next execution date will be the next Wednesday.

Note: When you force a job to run, the job is executed in your current session. Running the job reinitializes your session's packages.

Restrictions

You can only run 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 following statement runs job 14144 in your session and recomputes the next execution date:

DBMS_JOB.RUN(14144);

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

Terminating a Job

You can terminate 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 Oracle7 does not attempt to run the job again.

After you have identified the session running the job (via V$SESSION), you can disconnect the session using the Server Manager Disconnect Session menu item, or the SQL command ALTER SYSTEM.

See Also: For examples of viewing information about jobs and sessions, see "Viewing Job Queue Information" .

For more information on V$SESSION, see the Oracle7 Server Reference.

Procedure	Description	Described
SUBMIT	Submits a job to the job queue.
REMOVE	Removes specified job from the job queue.
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.
WHAT	Alters the job description for a specified job.
NEXT_DATE	Alters the next execution time for a specified job.
INTERVAL	Alters the interval between executions for a specified job.
BROKEN	Disables job execution. If a job is marked as broken, Oracle7 does not attempt to execute it.
RUN	Forces a specified job to run.

Parameter	Description
`job`	This is the identifier assigned to the job you created. You must use the job number whenever you want to alter or remove the job.
	For more information about job numbers, see "Job Numbers" .
`what`	This is the PL/SQL code you want to have executed.
	For more information about defining a job, see "Job Definitions" .
`next_date`	This is the next date when the job will be run. The default value is SYSDATE.
`interval`	This is the date function that calculates the next time to execute the job. The default value is NULL. INTERVAL must evaluate to a future point in time or NULL.
	For more information on how to specify an execution interval, see .
`no_parse`	This is a flag. The default value is FALSE.
	If NO_PARSE is set to FALSE (the default), Oracle7 parses the procedure associated with the job. If NO_PARSE is set to TRUE, Oracle7 parses the procedure associated with the job the first time that the job is executed. If, for example, you want to submit a job before you have created the tables associated with the job, set NO_PARSE to TRUE.

Parameter	Mode	Description
`job`	IN	The number of the current job.
`next_date`	IN/OUT	The date of the next execution of the job. The default value is SYSDATE.
`broken`	IN/OUT	Status of job, broken or not broken. The IN value is FALSE.

Date Expression	Evaluation
`'SYSDATE + 7'`	exactly seven days from the last execution
`'SYSDATE + 1/48'`	every half hour
`'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