A Scheduler job that fails does not tell anyone by itself. The error is recorded in the job log, and the job tries again at its next run. To manage failures you need to know where to look, and how to stop a job that keeps failing: the MAX_FAILURES attribute marks it broken after a number of failures in a row.
Code for This Guide
The main examples are in the examples/pkg-scheduler folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
-- the history of runs, with errors select job_name, status, error#, errors from user_scheduler_job_run_details; dbms_scheduler.set_attribute(job_name, 'max_failures', n); dbms_scheduler.set_attribute(job_name, 'max_run_duration', interval); dbms_scheduler.set_attribute(job_name, 'logging_level', dbms_scheduler.logging_full);
Find Out Why a Job Failed
The job raises an error and runs once, at once, because it has no schedule.
Example:
begin
dbms_scheduler.create_job(
job_name => 'FAILING_JOB',
job_type => 'PLSQL_BLOCK',
job_action => q'[begin
raise_application_error(-20001, 'fuel price feed missing');
end;]',
enabled => true); -- no schedule: runs once, at once
end;
/
exec dbms_session.sleep(5)
select job_name, status, error#, substr(errors, 1, instr(errors, chr(10)) - 1) as error
from user_scheduler_job_run_details
where job_name = 'FAILING_JOB'
and log_date > systimestamp - interval '1' minute;Output:
PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. JOB_NAME STATUS ERROR# ERROR ______________ _________ _________ _____________________________________ FAILING_JOB FAILED 20001 ORA-20001: fuel price feed missing
The run is logged as FAILED with error 20001, and the ERRORS column holds the error stack; the query shows its first line. The job runs in its own session, so the calling session gets no error at all.
Stop Retrying After Repeated Failures
FLAKY_FEED runs every 2 seconds and always fails. With MAX_FAILURES set to 2, the Scheduler gives up after two failures in a row.
Example:
begin
dbms_scheduler.create_job(
job_name => 'FLAKY_FEED',
job_type => 'PLSQL_BLOCK',
job_action => q'[begin raise_application_error(-20002, 'feed down'); end;]',
repeat_interval => 'FREQ=SECONDLY; INTERVAL=2',
enabled => false);
dbms_scheduler.set_attribute('FLAKY_FEED', 'max_failures', 2);
dbms_scheduler.enable('FLAKY_FEED');
end;
/
exec dbms_session.sleep(10)
-- after two failures in a row the job is BROKEN and stops running
select job_name, state, failure_count, enabled
from user_scheduler_jobs
where job_name = 'FLAKY_FEED';Output:
PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. JOB_NAME STATE FAILURE_COUNT ENABLED _____________ _________ ________________ __________ FLAKY_FEED BROKEN 2 FALSE
The job is BROKEN and disabled, with a failure count of 2. Fix the cause, then ENABLE the job to start it again. The example drops the job afterward.
Things to Know
- MAX_RUN_DURATION raises an event when a run takes too long, and LOGGING_FULL records every change to the job as well as its runs.
- RESTARTABLE TRUE makes a job retry a failed run instead of waiting for the next scheduled time.
- For alerts, ADD_JOB_EMAIL_NOTIFICATION sends e-mail on events such as JOB_FAILED and JOB_BROKEN.
Related Guides
Conclusion
Scheduler job failures are recorded in USER_SCHEDULER_JOB_RUN_DETAILS with the error number and stack. Check it after failures, set MAX_FAILURES so a failing job stops instead of retrying forever, and add notifications for the jobs that matter.
