Viewing and Managing Job Queues in Oracle 9i

January 3, 2003

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.