Another DBMS_Scheduler Chain Rule Issue

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 …

  1. Do not rely on Oracle validating rule and step modifications.
  2. Watch the first execution of a job on which you have modified the rules like a hawk in case you forgot point 1.

And Another Thing …

Following on from my recent complaint about an all-time low on the Oracle Forums, does anyone else get the impression that the work there is not just answering questions, but seems increasingly to be correcting all of the incorrect answers?

Obviously I have an example in mind.

Or has it always been thus?