DBMS_SCHEDULER Sub-Chains Fail With ORA-01426 When SCHEDULER$_INSTANCE_S Sequence > (2^31)/2

(Doc ID 2358206.1)

Last updated on FEBRUARY 07, 2018

Applies to:

Oracle Database - Enterprise Edition - Version 12.1.0.2 and later
Information in this document applies to any platform.

Symptoms

DBMS_SCHEDULER sub_chains (chains called from a "parent" chain) are failing when the SCHEDULER$_INSTANCE_S sequence has exceeded (2^31)/2.

The chain reports an error_code of 1426 in DBA_SCHEDULER_RUNNING_CHAINS and tracing with the 27402 event reveals this:

--------------
Starting step ENTITY_GROUP of job DW3.REFRESH_DAILY_TABLES.DAILY_REFRESH_SERIES at 24-JUL-17 00.49.13.505000000 +01:00
Job step creation failed at 24-JUL-17 00.49.13.505000000 +01:00. See error below.
ORA-01426: numeric overflow
Done with evaluation at 24-JUL-17 00.49.13.505000000 +01:00 with 357 rules evaluated to TRUE
--------------

and occasionally:

--------------
Starting step DAILY_REFRESH_DUAL of job DW3.REFRESH_DAILY_TABLES. at 24-JUL-17 12.35.35.665000000 AM +01:00
Job step creation failed at 24-JUL-17 12.35.35.665000000 AM +01:00. See error below.
ORA-01426: numeric overflow
Evaluation raised error.ORA-01426: numeric overflow
ORA-06512: at "SYS.DBMS_ISCHED", line 5349
ORA-01426: numeric overflow
Released evaluation lock ORA$_SCHED_EVAL_1095669254 at 24-JUL-17 12.35.35.665000000 AM +01:00 with error 0
--------------

Dropping this sequence and recreating it starting with 1 fixes this issue and all chains run successfully again.


Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms