#DICA - Erro ORA-01873 ao consultar um job

Na dica de hoje, estarei falando sobre um erro que ocorreu ao consultar um job na dba_jobs, após o ambiente migrar do Oracle 11.2.0.4 para 19C.

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

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

SQL> 

De acordo com a Oracle, este comportamento ocorre pois no 19c a consulta do total_time na visão dba_jobs foi alterada. Na nova consulta se o intervalo entre o last_end_date o last_start_date for superior a 7 horas, ela excederá a precisão de 9 dígitos, gerando um erro ORA-01873.

Abaixo vemos o DDL da dba_jobs da versão 11g.

select JOB, lowner LOG_USER, powner PRIV_USER, cowner SCHEMA_USER,
    LAST_DATE, substr(to_char(last_date,'HH24:MI:SS'),1,8) LAST_SEC,
    THIS_DATE, substr(to_char(this_date,'HH24:MI:SS'),1,8) THIS_SEC,
    NEXT_DATE, substr(to_char(next_date,'HH24:MI:SS'),1,8) NEXT_SEC,
    (total+(sysdate-nvl(this_date,sysdate)))*86400 TOTAL_TIME,
    decode(mod(FLAG,2),1,'Y',0,'N','?') BROKEN,
    INTERVAL# interval, FAILURES, WHAT,
    nlsenv NLS_ENV, env MISC_ENV, j.field1 INSTANCE
  from sys.job$ j
  where BITAND(j.scheduler_flags, 2) IS NULL OR
        BITAND(j.scheduler_flags, 2) = 0 /* don't show jobs with drop flag */

E agora no Oracle 19c. Veja que houve uma mudança na forma que o TOTAL_TIME é calculado.

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#

Para simular o erro é possível rodar a consulta do total_time, passando um intervalo superior a 7 horas. Veja:

SQL> select extract(day from (cast( to_date('10-01-24 18:00:00','yy-mm-dd hh24:mi:ss') as timestamp) - cast( to_date('10-01-24 12:00:00','yy-mm-dd hh24:mi:ss') as timestamp))*86000) from dual;

EXTRACT(DAYFROM(CAST(TO_DATE('10-01-2418:00:00','YY-MM-DDHH24:MI:SS')ASTIMESTAMP
--------------------------------------------------------------------------------
                                                                           21500

SQL> select extract(day from (cast( to_date('10-01-24 21:00:00','yy-mm-dd hh24:mi:ss') as timestamp) - cast( to_date('10-01-24 12:00:00','yy-mm-dd hh24:mi:ss') as timestamp))*86000) from dual;
select extract(day from (cast( to_date('10-01-24 21:00:00','yy-mm-dd hh24:mi:ss') as timestamp) - cast( to_date('10-01-24 12:00:00','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


SQL>

Como solução a oracle sugere ignorar o uso da coluna total_time ou utilizar a dba_scheduler_jobs. Porém vale destacar que na dba_scheduler_jobs não consta o  total\_time

Uma opção alternativa seria criar uma copia da view dba_jobs alterando a linha de total_time, conforme abaixo, onde foi criada uma dba_jobs2.

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, 
    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#