Querying DBA_JOBS Encounter ORA-01873 (Doc ID 2710794.1)

jiawang

In this Document
Symptoms
Changes
Cause
Solution
References


APPLIES TO:
Oracle Database - Enterprise Edition - Version 19.0.0.0 and later
Information in this document applies to any platform.
SYMPTOMS
Querying DBA_JOBS encounter ORA-01873 in 19c.




    ORA-01873: the leading precision of the interval is too small
CHANGES
Upgrade to 19c.


CAUSE
Please note that Scheduler will not track the TOTAL_TIME for Job.



Database Preupgrade tool check list. (Doc ID 2380601.1)
Starting with Oracle Database 19c, jobs created and managed through DBMS_JOB package in previous database versions
will be re-created using Oracle Scheduler architecture. Jobs not successfully re-created may not function properly after upgrade.


In 19c, the definition of the TOTAL_TIME field in dba_jobs is as follows:


  (CASE WHEN j.last_end_date>j.last_start_date THEN
   extract(day from (j.last_end_date-j.last_start_date)*86400) ELSE 0 END)
   TOTAL_TIME, -- Scheduler does not track total time
For the above formula, if the gap between last_end_date and last_start_date is more than 7 hours, and the difference is multiplied by 86400, it will exceed the precision of 9-digit integer, and an error ORA-01873 will be reported.


SQL> select extract(day from (cast( to_date('21-12-21 17:15:09','yy-mm-dd hh24:mi:ss') as timestamp) - cast( to_date('21-12-21 11:15:09','yy-mm-dd hh24:mi:ss') as timestamp))*86000) from dual;


EXTRACT(DAYFROM(CAST(TO_DATE('21-12-2117:15:09','YY-MM-DDHH24:MI:SS')ASTIMESTAMP
--------------------------------------------------------------------------------
                                                                          21500


SQL> select extract(day from (cast( to_date('21-12-21 17:15:09','yy-mm-dd hh24:mi:ss') as timestamp) - cast( to_date('21-12-21 10:15:09','yy-mm-dd hh24:mi:ss') as timestamp))*86000) from dual;
select extract(day from (cast( to_date('21-12-21 17:15:09','yy-mm-dd hh24:mi:ss') as timestamp) - cast( to_date('21-12-21 10:15:09','yy-mm-dd hh24:mi:ss') as timestamp))*86000) from dual
                                                                                                                                                                        *
ERROR at line 1:
ORA-01873: the leading precision of the interval is too small
Therefore, if the dictionary data is upgrade/migrate to 19c, ORA-01873 may occur.


SOLUTION
Please ignore column TOTAL_TIME and select other columns to get the information from querying DBA_JOBS.


OR


Querying dba_scheduler_jobs and you will see all of the jobs  that were created using dbms_job.


    select * from dba_scheduler_jobs;