Jobs Fails With Ora-01426 - Numeric Overflow - After Upgrade To 11.2 (Doc ID 1267158.1)

Last updated on DECEMBER 06, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.1 and later
Information in this document applies to any platform.
***Checked for relevance on 11-Jul-2011***

Symptoms

Jobs (both dbms_scheduler and dbms_jobs jobs) are failing with ORA-01426: Numeric overflow.

Alert log shows job queue slave (J00x) reporting the following messages:

ORA-12012: error on auto execute of job 4294967294
ORA-01426: numeric overflow
ORA-01426: numeric overflow
ORA-01426: numeric overflow

The job number reported is 4294967294 yet the current job sequence number (JOBSEQ from dba_sequences) is 21310.

Events '1426 trace name errorstack level 3' or '10046 trace name context forever, level 4' shows the following SQL statement failing with ORA-1426 error.

select next_run_date, obj#, run_job, sch_job from (select decode(bitand(a.flags, 16384), 0, a.next_run_date, a.last_enabled_time) next_run_date, a.obj# obj#, decode(bitand(a.flags, 16384), 0, 0, 1) run_job, a.sch_job sch_job from (select p.obj# obj#, p.flags flags, p.next_run_date next_run_date,p.job_status job_status, p.class_oid class_oid,p.last_enabled_time last_enabled_time, p.instance_id instance_id,1 sch_job from sys.scheduler$_job p where bitand(p.job_status, 3) = 1 and ((bitand(p.flags, 134217728 + 268435456) = 0) or (bitand(p.job_status, 1024) <> 0)) and bitand(p.flags, 4096) = 0 and p.instance_id is NULL and (p.class_oid is null or (p.class_oid is not null and p.class_oid in (select b.obj# from sys.scheduler$_class b where b.affinity is null))) UNION ALL select q.obj#, q.flags, q.next_run_date, q.job_status, q.class_oid, q.last_enabled_time, q.instance_id, 1 from sys.scheduler$_lightweight_job q where bitand(q.job_status, 3) = 1 and ((bitand(q.flags, 134217728 + 268435456) = 0) or (bitand(q.job_status, 1024) <> 0)) and bitand(q.flags, 4096) = 0 and q.instance_id is NULL and (q.class_oid is null or (q.class_oid is not null and q.class_oid in (select c.obj# from sys.scheduler$_class c where c.affinity is null))) UNION ALL select j.job, 0, from_tz(cast(j.next_date as timestamp), to_char(systimestamp,'TZH:TZM')), 1, NULL, from_tz(cast(j.next_date as timestamp),to_char(systimestamp,'TZH:TZM')), NULL, 0 from sys.job$ j where (j.field1 is null or j.field1 = 0) and j.this_date is null) a order by 1) where rownum = 1
/

jskcStartTimeSelect returned error 1426
jskcSartTimeSelect: err =1426
CLOSE #3:c=0,e=6,dep=1,type=0,tim=1653317076502
EXEC #2:c=20000,e=30846,p=0,cr=82,cu=9,mis=0,r=0,dep=0,og=1,plh=0,tim=1653317076555
ERROR #2:err=1426 tim=1653317076575


Yet, the above SQL statement runs fine in SQL*Plus or SQL*Developer.

NEXT_RUN_DATE                      OBJ#    RUN_JOB SCH_JOB
-------------                  --------  -----------  -------------
10.10.08 11:09:28,000000000 +00:00 -4       0             0



Changes

The database was recently upgraded from 9.2.0.8 to 11.2.0.1.

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