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;
