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 4

By DatabaseJournal.com Staff

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



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