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, 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

3 Comments »

  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

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

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


RSS feed for comments on this post. TrackBack URI

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

The Silver is the New Black Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 202 other followers

%d bloggers like this: