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 - Page 5

By DatabaseJournal.com Staff

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.



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