Dear DBA_SCHEDULER_RUNNING_JOBS, where have you been?!

originally published at Pythian
(story based on real events)
Where: Meeting room, Pythian office, Sydney, Australia.
When: Afternoon, after lunch time.
What: Discussion of planned maintenance for one of clients.

DBA1: We should check DBA_JOBS_RUNNING and DBA_SCHEDULER_RUNNING_JOBS
that there are no running jobs. And if there are no any we should continue with next step of our plan.

DBA2: Do you mean V$SCHEDULER_RUNNING_JOBS instead of DBA_SCHEDULER_RUNNING_JOBS?

DBA1: No, why?

DBA2: Because there is no such view in dictionary.

DBA1: But it is there in database. (He connects to sqlplus and issues
select count(*) from DBA_SCHEDULER_RUNNING_JOBS. It immediately returns 0 rows)

DBA2: (His eyes are ready to jump out of eye sockets. He almost pulls out laptop from DBA1 hands
and murming starts to type commands in sqlplus) ...How that can be... ...not in dict... not in dictionary...
...it sent word...(He typed select table_name from dict where table_name like 'DBA_SCHEDULER%')
You see, it is not in a dictionary although there are others and I thought it does not exist, until now...
(pause)
Excuse me my dear friend (DBA2 appealing to DBA1) how about we continue discussion later
since there should be a reason of such behaviour and I have to reveal it...
(He runs away from the room to find the reason of
DBA_SCHEDULER_RUNNING_JOBS disapperance from dictionary view)
(scene finished)

That happened today and I am being DBA2 started to check what's the secret hides the view.
I checked documentation - description of the view is there, I checked MOS - search did not show anything
related, nothing was found through Internet search either. So I decided to dig into dictionary view
and using select dbms_metadata.get_ddl('VIEW', 'DICTIONARY') from dual got the first part of the query
where sys.obj$ and sys.com$ tables were joined.

CREATE OR REPLACE FORCE VIEW "SYS"."DICTIONARY" ("TABLE_NAME", "COMMENTS") AS
select o.name, c.comment$
from sys.obj$ o, sys.com$ c
where o.obj# = c.obj#(+)
and c.col# is null
and o.owner# = 0
and o.type# = 4
and (o.name like 'USER%'
or o.name like 'ALL%'
or (o.name like 'DBA%'
and exists
(select null
from sys.v$enabledprivs
where priv_number = -47 /* SELECT ANY TABLE */)
)
)
union all
...

DBA_SCHEDULER_RUNNING_JOBS was not shown there, but there were other DBA_SCHEDULER... views
and investigating further I found the reason why the view is not shown in dict view
(which is synonym on dictionary).
Simply because there is no entry with col# equals to NULL for the object DBA_SCHEDULER_RUNNING_JOBS.
NULL value in col# is just identification of a comment for a table and obviously that comment was missed
for our loss.

SQL> select col#, obj#, comment$ from com$ where obj# = (select obj# from obj$
where name = 'DBA_JOBS_RUNNING' and owner# = 0) and col# is null;

COL# OBJ# COMMENT$
---------- ---------- ------------------------------------------------------------------------------------------
4828 All jobs in the database which are currently running, join v$lock and job$

SQL> select col#, obj#, comment$ from com$ where obj# = (select obj# from obj$
where name = 'DBA_SCHEDULER_RUNNING_JOBS' and owner# = 0) and col# is null;

no rows selected

SQL> select col#, obj#, comment$ from com$ where obj# = (select obj# from obj$
where name = 'DBA_SCHEDULER_RUNNING_JOBS' and owner# = 0);

COL# OBJ# COMMENT$
---------- ---------- ------------------------------------------------------------------------------------------
1 9386 Owner of the running scheduler job
2 9386 Name of the running scheduler job
3 9386 Subname of the running scheduler job (for a job running a chain step)
4 9386 Job style - regular, lightweight or volatile
7 9386 Process number of the slave process running the scheduler job
8 9386 Operating system process number of the slave process running the scheduler job
9 9386 Database instance number of the slave process running the scheduler job
10 9386 Resource consumer group of the session in which the scheduler job is running
11 9386 Time elapsed since the scheduler job started
12 9386 CPU time used by the running scheduler job, if available
13 9386 Owner of destination object (if used) else NULL
14 9386 Destination that this job is running on
15 9386 Owner of login credential used for this running job, if any
16 9386 Name of login credential used for this running job, if any
17 9386 Log id that will be used for this job run

15 rows selected.

I also checked $ORACLE_HOME/rdbms/admin and found the file
where "comment on table dba_scheduler_running_jobs is"
was missed - catschv.sql, looks like a bug for me...

Have a good day!