Coskan’s Approach to Oracle

October 23, 2008

Dictionary views which are not listed in Dictionary View

Filed under: Basics, Tips — coskan @ 4:20 pm

I usually check view called dictionary if I want to learn the name of the dictionary table which I need to query for specific info.

For example if I want to learn which views are available to view SCHEDULER related info I use query below ,

select * from dict where table_name like ‘%SCHED%’;

Then I do my research by querying  the table in the result set.

Today I was looking for which table I need to look for running scheduled jobs I realized that documented

DBA_SCHEDULER_RUNNING_JOBS view is not listed in Dictionary view, I crosschecked DBA_OBJECTS view to see if it is listed there. It was listed under DBA_OBJECTS as both  view and synonym. So what was the problem ?

In the definition of Dictionary view what you see is a union query. First part of the union is like below.

SELECT o.NAME, c.comment$
FROM SYS.obj$ o,$ c
WHERE o.obj# = c.obj#(+)
AND c.col# IS NULL
AND o.owner# = 0
AND o.type# = 4
OR (    o.NAME LIKE ‘DBA%’
FROM SYS.v$enabledprivs
WHERE priv_number = -47 /* SELECT ANY TABLE */)



DBA_SCHEDULER_RUNNING_JOBS view is definitely listed under SYS.obj$ so there must be a problem on$ table which holds the comments of both tables and columns.

When I query$ with the obj# of DBA_SCHEDULER_RUNNING_JOBS view, I realized that, there isn’t any row where c.col# IS NULL ( I assume the row that holds table comment. )

I think Oracle developers forgot to put a comment on this table. Further investigation was to see which dictionary tables were missed by dictionary table. Basic minus operation lists 36 tables that are missed by Dictionary view on

As a workaround You can still query DBA_OBJECTS but it doesn’t have an indicator that the listed is a dictionary table so you might miss the ones you look for.

To solve the problem I created a DICT_MISSED table and put it as another UNION_ALL statement at the and of the DICTIONARY view source and created MYDICT view. The only problem is, you must refresh DICT_MISSED on every upgrade to catch new missed tables (I hope there will be none if someone from oracle reads this).

Full demonstration scripts

MYDICT view Script.

Note:  WordPressed changed a lot since I wrote for the last time Its a shame that they dont support txt upload anymore pufff



  1. Hey, welcome back! 🙂

    Comment by H.Tonguç Yılmaz — October 25, 2008 @ 5:41 am

  2. You can file a bug with Oracle via Metalink and point this to them.

    Comment by Vishal Gupta — October 25, 2008 @ 9:28 am

    FROM SYS.obj$
    WHERE owner# = 0 AND
    type# = 4 AND
    SubStr(NAME,1,4) IN ( ‘USER’,’ALL_’,’DBA_’)
    ORDER BY 1

    Comment by Helio Geminiano — December 12, 2008 @ 5:39 pm

  4. If you just simply need something to convert Word to PDF only, Nemo Word to PDF will be the
    good alternative. MS word is as indigenous to any computer
    as language is to humans. Public speaking is a form of communication; acting is a form of entertainment, although one would hope that your delivery of
    a speech or presentation is entertaining as well.

    Comment by — May 11, 2016 @ 6:41 am

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

Blog at

%d bloggers like this: