Dba_jobs and dba_jobs_running different results.
Reference text:
Different Results from Dba_jobs and dba_jobs_running (Doc ID 109725.1)
Objective:
Explain the relationship between Dba_jobs and dba_jobs_running.
Range:
Dba
You find that you can query a record in dba_jobs_running, but you can't find the record from Dba_jobs.
You are concerned about data dictionary (DD) corruption.
Explain:
Definition of Dba_jobs:
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
Definition of dba_jobs_running:
Select V.sid, V.id2 JOB, J.failures,
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,
J.field1 INSTANCE
From Sys.job$ J, V$lock V
where V.type = ' JQ ' and j.job (+) = V.id2
In the Where condition of dba_jobs_running, there is an interesting outer join. The meaning of this outer connection is
Dba_jobs_running shows those that are not in sys.job$ but can be seen in V$lock (job is running)
For example, the situation may be that the job has just been removed and the job is not running yet.