Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Jan 3, 2003

Viewing and Managing Job Queues in Oracle 9i

By DatabaseJournal.com Staff

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.


Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM


















Thanks for your registration, follow us on our social networks to keep up-to-date