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.
