Following on from a recent adventure with a problem validating DBMS_Scheduler chain rules, I hit another issue today.
A rule was defined with a step name that does not exist. This happened because there is an odd limit of chain step name lengths (27 bytes I think), and the name of the step in the define_step call was modified from STP_EXP_SEND_FILES_TO_PROBES to STP_EXP_SEND_FILES_TO_PR after the rule code was written to reference STP_EXP_SEND_FILES_TO_PROBES.
When the job was executed the scheduler made repeated efforts to start a job_subname of STP_EXP_SEND_FILES_TO_PROBES_0 (that’s a zero on the end) approximately every 0.005 second, with the job run details reporting a status of FAILED and additional_info of:
CHAIN_LOG_ID=”576839″,STEP_NAME=”STP_EXP_SEND_FILES_TO_PROBES”, ORA-01403: no data found.
The job had to be stopped using FORCE=TRUE, dropped and recreated.
When the rule was corrected the job ran correctly.
Of course this has left us with rather large SYS.SCHEDULER$_JOB_RUN_DETAILS, SYS.SCHEDULER$_EVENT_LOG and SYS.SCHEDULER$_INSTANCE_PK segments, which is not ideal. We’ll see if support will let us run a shrink space on them.
So, to the lessons …
- Do not rely on Oracle validating rule and step modifications.
- Watch the first execution of a job on which you have modified the rules like a hawk in case you forgot point 1.
The Oracle Sponge 