ORA01873 故障处理

错误解释

oerr ora 1873 查看错误解释
01873, 00000, “the leading precision of the interval is too small” “区间间隔精度太小”
// *Cause: The leading precision of the interval is too small to store the specified interval.
// *Action: Increase the leading precision of the interval or specify an interval with a smaller leading precision.
//*原因：间隔的前导精度太小，无法存储指定的间隔。
//*操作：增加间隔的前导精度或指定前导精度较小的间隔。

背景

某用户反馈查询定时任务时数据库报错ORA-01873的问题。这个问题导致无法查看和管理定时任务。
发现问题是由于定时任务的视图DBA_JOB的定义中，时间间隔的计算方式有BUG所导致的。经过充分测试手动调整了DBA_JOBS视图的计算方式。通过这一调整，我们成功地解决了这个问题，保障了系统的稳定健康运行。

根本原因

问题的根本原因是Oracle计算两个timestamp时间戳的间隔时间，再乘上86400(1天的秒数)超出了时间间隔类型(interval)的精度范围，出现查询报错。

问题复现

SYS@ORCL(orcl): 2> select * from dba_jobs;
ERROR:
ORA-01873: the leading precision of the interval is too small

查询DBA_JOBS问题同样复现，测试定位到是TOTAL_TIME字段引起的报错

原DBA_JOBS视图定义

DBA_JOBS是一个视图，通过DBA_VIEWS视图查看定时任务视图的定义，查看TOTAL_TIME的计算公式：
使用两个时间戳相减，再乘上86400，最后提取出天数（由于已经在内部乘上了1天的86400秒，所以提取出来的天数就相当于实际的秒数）

某个JOB任务执行了20.5天，20.5天*86400 = 2160000 (216万天) 超出了interval可显示天数据的精度范围；定位了问题的根本原因

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


解决方案

由于是DBA_JOBS视图默认TOTAL_TIME计算方式存在问题，可以通过手动调整视图定义，来调整时间的计算方法。通过以小数表示的天数乘上每天的秒数(86400)，可以计算出相差的秒数。通过如下调整，问题得以解决：

alter session set "_oracle_script"=true;
create or replace view sys.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#
;