Viewing and Managing Job Queues in Oracle 9i

Friday Jan 3rd 2003 by DatabaseJournal.com Staff
Share:

Learn how to schedule the periodic execution of user jobs using job queues in Oracle 9i. Also covered are topics on viewing job queue information and managing job queues.

Ajay Gursahani, ajay.gursahani@mahindrabt.com

This article describes how to use job queues to schedule the periodic execution of user jobs, and contains the following topics:

  • Viewing Job Queue Information
  • Managing Job Queues

Overview

Routines (jobs) can be scheduled to run periodically using the job queue. To schedule a job, simply submit it to the job queue using the Oracle supplied DBMS_JOB package, and specify the frequency at which the job is to be run. Additional functionality enables you to alter, disable, or delete a job that was previously submitted.


Viewing Job Queue Information

ALL view describes all of the jobs that are accessible to the current user, and the USER view describes all of the jobs owned by the current user.

The following is the structure of dba_jobs:

JOB                             	NOT NULL NUMBER
LOG_USER                       		NOT NULL VARCHAR2(30)
PRIV_USER                      		NOT NULL VARCHAR2(30)
SCHEMA_USER               		NOT NULL VARCHAR2(30)
LAST_DATE                     		DATE
LAST_SEC                        	VARCHAR2(8)
THIS_DATE                      		DATE
THIS_SEC                          	VARCHAR2(8)
NEXT_DATE                     		NOT NULL DATE
NEXT_SEC                        	NOT NULL VARCHAR2(8)
TOTAL_TIME                   		NOT NULL NUMBER
BROKEN                          	NOT NULL VARCHAR2(1)
INTERVAL                       		NOT NULL VARCHAR2(200)
FAILURES                        	NUMBER
WHAT                       		VARCHAR2(4000)
CURRENT_SESSION_LABEL       		RAW MLSLABEL
CLEARANCE_HI                        	RAW MLSLABEL
CLEARANCE_LO                        	RAW MLSLABEL
NLS_ENV                             	VARCHAR2(4000)
MISC_ENV                            	RAW(32)

Displaying Information about a Job

The following query creates a listing of the job number, description, next execution time and broken status for each job you have submitted:

SELECT JOB, SUBSTR(WHAT,1,35), NEXT_DATE, NEXT_SEC, BROKEN FROM DBA_JOBS;

JOB	SUBSTR(WHAT,1,35)	NEXT_DATE 	NEXT_SEC	BROKEN
----------------------------------------------------------------------
1	HOUSEKEEP.DEL001;	08/11/2002 	12:35:22	N
2	HOUSEKEEP.DEL002;	08/11/2002 	12:39:45	N
3	HOUSEKEEP.DEL003;	10/04/2001 	01:01:03	N
4	DEPT.DELREC;		10/04/2001 	01:01:03	N

4 rows selected.
Ajay Gursahani, ajay.gursahani@mahindrabt.com

Managing Job Queues

This section describes the various aspects of managing job queues through the DBMS_JOB package viz:

  • Submitting a Job to the Job Queue
  • Removing a Job from the Job Queue
  • Altering a Job
  • Broken Jobs
  • Forcing a Job to Execute

The DBMS_JOB package is used to schedule and manage jobs in the job queue. To achieve this we have to use procedures supplied under the DBMS_JOB package.


DBMS_JOB.SUBMIT

To submit a job to the job queue, use the following syntax:

DBMS_JOB.SUBMIT ( JOB OUT BINARY_INTEGER,
                  WHAT IN VARCHAR2, 
                  NEXT_DATE IN DATE DEFAULTSYSDATE, 
                  INTERVAL IN VARCHAR2 DEFAULT 'NULL',
                  NO_PARSE IN BOOLEAN DEFAULT FALSE,
                  INSTANCE IN BINARY_INTEGER DEFAULT ANY_INSTANCE,
                  FORCE IN BOOLEAN DEFAULT FALSE);
job - An output parameter. This is the identifier assigned to the job you are creating. You must use this job number whenever you want to alter or remove the job.

what - The PL/SQL code you want to execute.

next_date - The next date when the job will be run. The default value is SYSDATE.

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

no_parse - If FALSE (default), then Oracle parses the associated procedure with the job. If TRUE, then the procedure is parsed only the first time the job is run.

instance - Specifies which instance can execute the job

force - If FALSE, then the instance must already be running or else it will return an error.


Example:

VARIABLE jobno NUMBER 
BEGIN
DBMS_JOB.SUBMIT (:jobno,'HOUSEKEEP.DEL004;',SYSDATE,'SYSDATE+(59/(24*60*60))');
COMMIT;
END;
/
PRINT jobno

JOBNO
----------
5

In the above example we are scheduling a job DEL004. This is basically a procedure located in package HOUSEKEEP. The interval at which the job should be executed is 60 seconds.

SELECT JOB, SUBSTR(WHAT,1,35), NEXT_DATE, NEXT_SEC, INTERVAL FROM DBA_JOBS;

JOB	SUBSTR(WHAT,1,35)	NEXT_DATE 	NEXT_SEC	INTERVAL 
-----------------------------------------------------------------------
1	HOUSEKEEP.DEL001;	08/11/2002 	12:35:22	sysdate+(59/(24*60*60))	
2	HOUSEKEEP.DEL002;	08/11/2002 	12:39:45	sysdate+(59/(24*60*60))
3	HOUSEKEEP.DEL003;	10/04/2001 	01:01:03	sysdate+(59/(24*60*60))	
4	DEPT.DELREC;		10/04/2001 	01:01:03	sysdate+(59/(24*60*60))	
5	HOUSEKEEP.DEL004;	10/04/2001 	01:01:03	sysdate+(59/(24*60*60))	

5 rows selected.

DBMS_JOB.REMOVE

Removes a specified job from the job queue.

DBMS_JOB.REMOVE (job IN  BINARY_INTEGER );

job - The number of the job already held in the job queue.


Example:

EXECUTE DBMS_JOB.REMOVE(2); 

SELECT JOB, SUBSTR(WHAT,1,35), NEXT_DATE, NEXT_SEC, INTERVAL FROM DBA_JOBS;

JOB	SUBSTR(WHAT,1,35)	NEXT_DATE 	NEXT_SEC	INTERVAL 
-----------------------------------------------------------------------
1	HOUSEKEEP.DEL001;	08/11/2002 	12:35:22	sysdate+(59/(24*60*60))	
3	HOUSEKEEP.DEL003;	10/04/2001 	01:01:03	sysdate+(59/(24*60*60))	
4	DEPT.DELREC;		10/04/2001 	01:01:03	sysdate+(59/(24*60*60))	
5	HOUSEKEEP.DEL004;	10/04/2001 	01:01:03	sysdate+(59/(24*60*60))	

4 rows selected.
Ajay Gursahani, ajay.gursahani@mahindrabt.com

DBMS_JOB.CHANGE

Alters a specified job that has already been submitted to the job queue. The job description, the time at which the job will be run, the interval between executions of the job, and the instance and force of a job can all be altered.

DBMS_JOB.CHANGE ( 
   job       IN  BINARY_INTEGER,
   what      IN  VARCHAR2,
   next_date IN  DATE,
   interval  IN  VARCHAR2,
   instance  IN  BINARY_INTEGER DEFAULT NULL,
   force     IN  BOOLEAN DEFAULT FALSE);
job - Number of the job being run.

what - PL/SQL procedure to run.

next_date - Date of the next refresh.

interval - Date function evaluated immediately before the job starts running.

instance - When a job is submitted, specifies which instance can run the job. This defaults to NULL, which indicates that instance affinity is not changed.

force - If this is FALSE, then the specified instance must be running. Otherwise, the routine raises an exception. If this is TRUE, then any positive integer is acceptable as the job instance.


Example:

EXECUTE DBMS_JOB.CHANGE(1,null,sysdate+3,null); 

Changes the next_date of job number 1 from 08/11/2002 to (system date+3).
If system date is 15/12/2002, then the next_date will be 18/12/2002.

SELECT JOB, SUBSTR(WHAT,1,35), NEXT_DATE, NEXT_SEC, INTERVAL FROM DBA_JOBS;

JOB	SUBSTR(WHAT,1,35)	NEXT_DATE 	NEXT_SEC	INTERVAL 
-----------------------------------------------------------------------
1	HOUSEKEEP.DEL001;	18/12/2002 	12:35:22	sysdate+(59/(24*60*60))	
3	HOUSEKEEP.DEL003;	10/04/2001 	01:01:03	sysdate+(59/(24*60*60))	
4	DEPT.DELREC;		10/04/2001 	01:01:03	sysdate+(59/(24*60*60))	
5	HOUSEKEEP.DEL004;	10/04/2001 	01:01:03	sysdate+(59/(24*60*60))	

4 rows selected.

DBMS_JOB.WHAT

The definition of a job can be altered by calling the DBMS_JOB.WHAT procedure.

DBMS_JOB.CHANGE ( 
   job    IN  BINARY_INTEGER,
   what   IN  VARCHAR2);
job - Number of the job being run.

what - PL/SQL procedure to run.


Example:

The following example changes the definition for job 3. The procedure DEL003 is replaced by procedure DEL006.

execute DBMS_JOB.WHAT(3,'HOUSEKEEP.DEL006;');

SELECT JOB, SUBSTR(WHAT,1,35), NEXT_DATE, NEXT_SEC, INTERVAL FROM DBA_JOBS;


JOB	SUBSTR(WHAT,1,35)	NEXT_DATE 	NEXT_SEC	INTERVAL 
-----------------------------------------------------------------------
1	HOUSEKEEP.DEL001;	18/12/2002 	12:35:22	sysdate+(59/(24*60*60))	
3	HOUSEKEEP.DEL006;	10/04/2001 	01:01:03	sysdate+(59/(24*60*60))	
4	DEPT.DELREC;		10/04/2001 	01:01:03	sysdate+(59/(24*60*60))	
5	HOUSEKEEP.DEL004;	10/04/2001 	01:01:03	sysdate+(59/(24*60*60))	

4 rows selected.
Ajay Gursahani, ajay.gursahani@mahindrabt.com

DBMS_JOB.NEXT_DATE

Alters the next execution time for a specified job.


DBMS_JOB.NEXT_DATE (JOB IN BINARY_INTEGER,
                    NEXT_DATE  IN DATE);
job - Number of the job being run.

next_date - Date of next refresh.


Example:


execute DBMS_JOB.NEXT_DATE(4,SYSDATE+3);

If the system date is 15/12/2002, then sysdate+3 is 18/12/2002.



SELECT JOB, SUBSTR(WHAT,1,35), NEXT_DATE, NEXT_SEC, INTERVAL FROM DBA_JOBS;



JOB	SUBSTR(WHAT,1,35)	NEXT_DATE 	NEXT_SEC	INTERVAL 
-----------------------------------------------------------------------
1	HOUSEKEEP.DEL001;	18/12/2002 	12:35:22	sysdate+(59/(24*60*60))	
3	HOUSEKEEP.DEL006;	10/04/2001 	01:01:03	sysdate+(59/(24*60*60))	
4	DEPT.DELREC;		18/12/2002 	01:01:03	sysdate+(59/(24*60*60))	
5	HOUSEKEEP.DEL004;	10/04/2001 	01:01:03	sysdate+(59/(24*60*60))	

4 rows selected.

DBMS_JOB.INTERVAL

Alters the interval between executions for a specified job.

DBMS_JOB.INTERVAL (job	     IN  BINARY_INTEGER,
                   interval  IN  VARCHAR2);
job - Number of the job being run.

interval - Character value that specifies the interval as a date expression.

If a job should be executed periodically at a set interval, use a date expression similar to 'SYSDATE + 7' in the INTERVAL parameter. Below are some common date expressions used for job execution intervals.

Exactly three days from the last execution
'SYSDATE + 3'

Every half hour
'SYSDATE + 1/48'

Every minute
'SYSDATE +(59/(24*60*60))'

Next Day -- Same time as last execution
'SYSDATE +(86400/(24*60*60))'

Every Friday at 3PM
'NEXT_DAY(TRUNC(SYSDATE),"FRIDAY") + 15/24'


Example:

execute DBMS_JOB.INTERVAL(5, 'sysdate+(1/48)');

Changes the interval of the job 5 from every minute to every half hour

SELECT JOB, SUBSTR(WHAT,1,35), NEXT_DATE, NEXT_SEC, INTERVAL FROM DBA_JOBS;

JOB	SUBSTR(WHAT,1,35)	NEXT_DATE 	NEXT_SEC	INTERVAL 
-----------------------------------------------------------------------
1	HOUSEKEEP.DEL001;	18/12/2002 	12:35:22	sysdate+(59/(24*60*60))	
3	HOUSEKEEP.DEL006;	10/04/2001 	01:01:03	sysdate+(59/(24*60*60))	
4	DEPT.DELREC;		18/12/2002 	01:01:03	sysdate+(59/(24*60*60))	
5	HOUSEKEEP.DEL004;	10/04/2001 	01:01:03	sysdate+(1/48)	

4 rows selected.

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

The INTERVAL date function is evaluated immediately before a job is executed. When the job completes successfully, the date calculated from INTERVAL becomes the new NEXT_DATE. 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 the INTERVAL date function evaluates to NULL and the job completes successfully, the job is deleted from the queue.

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")'.

Ajay Gursahani, ajay.gursahani@mahindrabt.com

DBMS_JOB.BROKEN


Sets or resets the job broken flag. If a job is marked as broken, Oracle does not attempt to execute it.





DBMS_JOB.BROKEN ( job in binary_integer,
		  Broken in boolea,
		  Next_date in date default sysdate);
job - Job being run.

broken - TRUE or FALSE.

next_date - Date of next refresh.

A job is labeled as either broken or not broken. When you submit a job, it is considered not broken. There are two ways a job can break:

  1. Oracle has failed to successfully execute the job after 16 attempts or
  2. You have marked the job as broken using the procedure DBMS_JOB.BROKEN

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.

Example:

execute DBMS_JOB.BROKEN(4, TRUE);

SELECT JOB, SUBSTR(WHAT,1,35), NEXT_DATE,BROKEN,INTERVAL FROM DBA_JOBS;

JOB	SUBSTR(WHAT,1,35)	NEXT_DATE 	BROKEN	INTERVAL 
-----------------------------------------------------------------------
1	HOUSEKEEP.DEL001;	18/12/2002 	N		sysdate+(59/(24*60*60))	
3	HOUSEKEEP.DEL006;	10/04/2001 	N		sysdate+(59/(24*60*60))	
4	DEPT.DELREC;		18/12/2002 	Y		sysdate+(59/(24*60*60))	
5	HOUSEKEEP.DEL004;	10/04/2001 	N		sysdate+(1/48)	

4 rows selected.
execute DBMS_JOB.BROKEN(4, FALSE, NEXT_DAY(SYSDATE, 'FRIDAY'));

The job 4 is marked as not broken, and its next execution date is set to the following Friday.


DBMS_JOB.RUN

Forces a specified job to run.

DBMS_JOB.RUN (job IN BINARY_INTEGER
              force IN BOOLEAN DEFAULT FALSE);
job - Number of the job being run

force - If FALSE, the specified instance must be already running or else will return an error


Example:

execute dbms_job.run(4);

This will force a job number 4 to be executed.


Summary

This article has discussed creating, viewing, and managing job queues in Oracle 9i for use in scheduling the periodic execution of user jobs. A number of examples have been covered to describe the various aspects of managing job queues through the DBMS_JOB package viz, including examples of submitting a job to the job queue, removing a job from the queue, altering jobs, broken jobs, and forcing jobs to execute.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved