Job Execution Interval:
‘SYSDATE + 7’ Exactly seven days from the last execution
‘SYSDATE + 1/48’ Every half hour
‘NEXT_DAY(TRUNC(SYSDATE), ”MONDAY”) + 15/24’ Every Monday at 3PM
‘NEXT_DAY(ADD_MONTHS(TRUNC(SYSDATE, ”Q”), 3), ”THURSDAY”)’ First Thursday of each quarter
Remobe the job:
BEGIN
DBMS_JOB.REMOVE(14144);
END;
/
Change the job:
In this example, job number 14144 is altered to execute every three days:
BEGIN
DBMS_JOB.CHANGE(14144, NULL, NULL, ‘SYSDATE + 3’);
END;
/
If you specify NULL for WHAT, NEXT_DATE, or INTERVAL when you call the procedure DBMS_JOB.CHANGE, the current value remains unchanged.
What is the job:
BEGIN
DBMS_JOB.WHAT(14144,
‘DBMS_DDL.ANALYZE_OBJECT(”TABLE”,
”HR”, ”DEPARTMENTS”,
”ESTIMATE”, NULL, 50);’);
END;
/
NEXT_DATE:
You can alter the next execution time for a job by calling the DBMS_JOB.NEXT_DATE procedure, as shown in the following example:
BEGIN
DBMS_JOB.NEXT_DATE(14144, SYSDATE + 4);
END;
/
INTERVAL:
The following example illustrates changing the execution interval for a job by calling the DBMS_JOB.INTERVAL procedure:
BEGIN
DBMS_JOB.INTERVAL(14144, ‘NULL’);
END;
/
Broken Jobs:
A job is labeled as either broken or not broken. Oracle does not attempt to run broken jobs. However, you can force a broken job to run by calling the procedure DBMS_JOB.RUN
You have marked the job as broken, using the procedure DBMS_JOB.BROKEN:
BEGIN
DBMS_JOB.BROKEN(14144, TRUE);
END;
/
BEGIN
DBMS_JOB.BROKEN(14144, FALSE, NEXT_DAY(SYSDATE, ‘MONDAY’));
END;
/
BEGIN
DBMS_JOB.RUN(14144);
END;
/
SELECT JOB, NEXT_DATE, NEXT_SEC, FAILURES, BROKEN
FROM DBA_JOBS;
JOB NEXT_DATE NEXT_SEC FAILURES B
——- ——— ——– ——– –
9125 01-JUN-01 00:00:00 4 N
14144 24-OCT-01 16:35:35 0 N
9127 01-JUN-01 00:00:00 16 Y
SELECT SID, r.JOB, LOG_USER, r.THIS_DATE, r.THIS_SEC
FROM DBA_JOBS_RUNNING r, DBA_JOBS j
WHERE r.JOB = j.JOB;
SID JOB LOG_USER THIS_DATE THIS_SEC
—– ———- ————- ——— ——–
12 14144 HR 24-OCT-94 17:21:24
25 8536 QS 24-OCT-94 16:45:12