Dictionary views which are not listed in Dictionary View

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, 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 view is definitely listed under SYS.obj$ so there must be a problem on SYS.com$ table which holds the comments of both tables and columns.

When I query SYS.com$ 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 10.2.0.4.

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

About these ads