ORA-01873: the leading precision of the interval is too small

 Hello,

Today we faced below error when quering dba_jobs:

SQL> select * from dba_jobs where job=4474623;
select * from dba_jobs where job=4474623

ORA-01873: the leading precision of the interval is too small

SQL> 

Oracle database is on 19c version so this dbms_job automatically converted in scheduler so this way it is correctly working:

select * from dba_scheduler_jobs where job_name='DBMS_JOB$_4474623';

so problem is dba_jobs view, we can see how it looks like :

create or replace view dba_jobs as
select
    m.dbms_job_number JOB, j.creator LOG_USER, u.name PRIV_USER,
    u.name SCHEMA_USER,
    CAST(j.last_start_date AS DATE) LAST_DATE,
    substr(to_char(j.last_start_date,'HH24:MI:SS'),1,8) LAST_SEC,
    CAST(
      DECODE(BITAND(j.job_status,2), 2, j.last_start_date, NULL)
    AS DATE) THIS_DATE,
    DECODE(BITAND(j.job_status,2), 2,
           substr(to_char(j.last_start_date,'HH24:MI:SS'),1,8), NULL) THIS_SEC,
    CAST(j.next_run_date AS DATE) NEXT_DATE,
    substr(to_char(j.next_run_date,'HH24:MI:SS'),1,8) NEXT_SEC,
    (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
    DECODE(BITAND(j.job_status,1),0,'Y','N') BROKEN,
    DECODE(BITAND(j.flags,1024+4096+134217728),
                  0, j.schedule_expr, NULL) INTERVAL,
    j.failure_count FAILURES, j.program_action WHAT,
    j.nls_env NLS_ENV, j.env MISC_ENV, NVL(j.instance_id, 0) INSTANCE
  from
    sys.scheduler$_dbmsjob_map m
    left outer join sys.obj$ o on (o.name = m.job_name)
    left outer join sys.user$ u on (u.name = m.job_owner)
    left outer join sys.scheduler$_job j on (j.obj# = o.obj#)
  where
    o.owner# = u.user#
;
comment on table DBA_JOBS is 'All jobs in the database';
comment on column DBA_JOBS.JOB is 'Identifier of job.  Neither import/export nor repeated executions change it.';
comment on column DBA_JOBS.LOG_USER is 'USER who was logged in when the job was submitted';
comment on column DBA_JOBS.PRIV_USER is 'USER whose default privileges apply to this job';
comment on column DBA_JOBS.SCHEMA_USER is 'select * from bar  means  select * from schema_user.bar ';
comment on column DBA_JOBS.LAST_DATE is 'Date that this job last successfully executed';
comment on column DBA_JOBS.LAST_SEC is 'Same as LAST_DATE.  This is when the last successful execution started.';
comment on column DBA_JOBS.THIS_DATE is 'Date that this job started executing (usually null if not executing)';
comment on column DBA_JOBS.THIS_SEC is 'Same as THIS_DATE.  This is when the last successful execution started.';
comment on column DBA_JOBS.NEXT_DATE is 'Date that this job will next be executed';
comment on column DBA_JOBS.NEXT_SEC is 'Same as NEXT_DATE.  The job becomes due for execution at this time.';
comment on column DBA_JOBS.TOTAL_TIME is 'Total wallclock time spent by the system on this job, in seconds';
comment on column DBA_JOBS.BROKEN is 'If Y, no attempt is being made to run this job.  See dbms_jobq.broken(job).';
comment on column DBA_JOBS.INTERVAL is 'A date function, evaluated at the start of execution, becomes next NEXT_DATE';
comment on column DBA_JOBS.FAILURES is 'How many times has this job started and failed since its last success?';
comment on column DBA_JOBS.WHAT is 'Body of the anonymous PL/SQL block that this job executes';
comment on column DBA_JOBS.NLS_ENV is 'alter session parameters describing the NLS environment of the job';
comment on column DBA_JOBS.MISC_ENV is 'a versioned raw maintained by the kernel, for other session parameters';
comment on column DBA_JOBS.INSTANCE is 'Instance number restricted to run the job';

So there is important comment on total time that -- Scheduler does not track total time.

Solution : 

we can change dba_jobs view with this query to solve this problem or you can query from dba_Scheduler_jobs, we are changing just total_time field calculation with this:

( CASE
              WHEN j.last_end_date > j.last_start_date THEN
                 --EXTRACT (
                 --DAY FROM (j.last_end_date - j.last_start_date) * 86400)
          (CAST(j.last_end_date AS DATE)-CAST(j.last_start_date AS DATE)) * 86400
              ELSE
                 0
           END )
     TOTAL_TIME, -- Scheduler does not track total time

So whole view will be like that :

create or replace view dba_jobs as
select
    m.dbms_job_number JOB, j.creator LOG_USER, u.name PRIV_USER,
    u.name SCHEMA_USER,
    CAST(j.last_start_date AS DATE) LAST_DATE,
    substr(to_char(j.last_start_date,'HH24:MI:SS'),1,8) LAST_SEC,
    CAST(
      DECODE(BITAND(j.job_status,2), 2, j.last_start_date, NULL)
    AS DATE) THIS_DATE,
    DECODE(BITAND(j.job_status,2), 2,
           substr(to_char(j.last_start_date,'HH24:MI:SS'),1,8), NULL) THIS_SEC,
    CAST(j.next_run_date AS DATE) NEXT_DATE,
    substr(to_char(j.next_run_date,'HH24:MI:SS'),1,8) NEXT_SEC,
    ( CASE
              WHEN j.last_end_date > j.last_start_date THEN
                 --EXTRACT (
                 --DAY FROM (j.last_end_date - j.last_start_date) * 86400)
          (CAST(j.last_end_date AS DATE)-CAST(j.last_start_date AS DATE)) * 86400
              ELSE
                 0
           END )
     TOTAL_TIME, -- Scheduler does not track total time
    DECODE(BITAND(j.job_status,1),0,'Y','N') BROKEN,
    DECODE(BITAND(j.flags,1024+4096+134217728),
                  0, j.schedule_expr, NULL) INTERVAL,
    j.failure_count FAILURES, j.program_action WHAT,
    j.nls_env NLS_ENV, j.env MISC_ENV, NVL(j.instance_id, 0) INSTANCE
  from
    sys.scheduler$_dbmsjob_map m
    left outer join sys.obj$ o on (o.name = m.job_name)
    left outer join sys.user$ u on (u.name = m.job_owner)
    left outer join sys.scheduler$_job j on (j.obj# = o.obj#)
  where
    o.owner# = u.user#
;
comment on table DBA_JOBS is 'All jobs in the database';
comment on column DBA_JOBS.JOB is 'Identifier of job.  Neither import/export nor repeated executions change it.';
comment on column DBA_JOBS.LOG_USER is 'USER who was logged in when the job was submitted';
comment on column DBA_JOBS.PRIV_USER is 'USER whose default privileges apply to this job';
comment on column DBA_JOBS.SCHEMA_USER is 'select * from bar  means  select * from schema_user.bar ';
comment on column DBA_JOBS.LAST_DATE is 'Date that this job last successfully executed';
comment on column DBA_JOBS.LAST_SEC is 'Same as LAST_DATE.  This is when the last successful execution started.';
comment on column DBA_JOBS.THIS_DATE is 'Date that this job started executing (usually null if not executing)';
comment on column DBA_JOBS.THIS_SEC is 'Same as THIS_DATE.  This is when the last successful execution started.';
comment on column DBA_JOBS.NEXT_DATE is 'Date that this job will next be executed';
comment on column DBA_JOBS.NEXT_SEC is 'Same as NEXT_DATE.  The job becomes due for execution at this time.';
comment on column DBA_JOBS.TOTAL_TIME is 'Total wallclock time spent by the system on this job, in seconds';
comment on column DBA_JOBS.BROKEN is 'If Y, no attempt is being made to run this job.  See dbms_jobq.broken(job).';
comment on column DBA_JOBS.INTERVAL is 'A date function, evaluated at the start of execution, becomes next NEXT_DATE';
comment on column DBA_JOBS.FAILURES is 'How many times has this job started and failed since its last success?';
comment on column DBA_JOBS.WHAT is 'Body of the anonymous PL/SQL block that this job executes';
comment on column DBA_JOBS.NLS_ENV is 'alter session parameters describing the NLS environment of the job';
comment on column DBA_JOBS.MISC_ENV is 'a versioned raw maintained by the kernel, for other session parameters';
comment on column DBA_JOBS.INSTANCE is 'Instance number restricted to run the job';

after change dba_jobs view we can query that job without any error:

SQL> select * from dba_jobs where job=4474623;