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")'.
» 
Comment and Contribute
