Dear DBA_SCHEDULER_RUNNING_JOBS, where have you been?!

Oct 12, 2011 / By Andrey Goryunov

Tags: ,

(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!

5 Responses to “Dear DBA_SCHEDULER_RUNNING_JOBS, where have you been?!”

  • Yury says:

    Thank you DBA2 from DBA1 :)

  • Noons says:

    Good catch, guys! This is truly a weird one!…

    Signed: DBA3
    ;-)

  • Just checked for others who lost
    27 more:

    1 select name from obj$ where obj# in (
    2 select obj# from com$ where obj# in (select obj# from obj$ where name like ‘DBA%’ and owner# = 0) group by obj#
    3 minus
    4 select obj# from com$ where obj# in (select obj# from obj$ where name like ‘DBA%’ and owner# = 0) and col# is null
    5 )
    6* and owner# = 0 and type# = 4
    d122> /

    NAME
    ——————————
    DBA_APPLICATION_ROLES
    DBA_SCHEDULER_RUNNING_JOBS
    DBA_SERVICES
    DBA_SQL_MANAGEMENT_CONFIG
    DBA_STREAMS_STMTS
    DBA_STREAMS_STMT_HANDLERS
    DBA_FREE_SPACE_COALESCED_TMP1
    DBA_FREE_SPACE_COALESCED_TMP2
    DBA_FREE_SPACE_COALESCED_TMP3
    DBA_FREE_SPACE_COALESCED_TMP4
    DBA_FREE_SPACE_COALESCED_TMP5
    DBA_FREE_SPACE_COALESCED_TMP6
    DBA_REDEFINITION_ERRORS
    DBA_REDEFINITION_OBJECTS
    DBA_REPCAT_REFRESH_TEMPLATES
    DBA_REPCAT_TEMPLATE_OBJECTS
    DBA_REPCAT_TEMPLATE_PARMS
    DBA_REPCAT_TEMPLATE_SITES
    DBA_REPCAT_USER_AUTHORIZATIONS
    DBA_REPCAT_USER_PARM_VALUES
    DBA_AUTO_SEGADV_CTL
    DBA_AUTO_SEGADV_SUMMARY
    DBA_SUMMARY_AGGREGATES
    DBA_SUMMARY_DETAIL_TABLES
    DBA_SUMMARY_JOINS
    DBA_SUMMARY_KEYS
    DBA_XSTREAM_INBOUND_PROGRESS
    DBA_XSTREAM_OUTBOUND_PROGRESS

    28 rows selected.

    Cheers,
    Andrey

  • Anand says:

    Hi,

    Nice one!!! I always use dba_servies but never checked whether its showed in dict or not.

    Anand

  • Andrey Goryunov says:

    I created SR and got bug 13107366 logged.
    Even severity was increased from 3 to 2 (by MOS)

    Andrey

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>