Viewing and Managing Job Queues in Oracle 9i - Page 4

January 3, 2003

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);

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);

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