Dict

Find Oracle data dictionary information, grouping the results into USER_/ALL_/DBA_/other. e.g. "@dict dict" finds DICT, DICTIONARY, DICT_COLUMNS, GV$LOGMNR_DICTIONARY and V$LOGMNR_DICTIONARY together with their descriptions.

-- dict.sql
-- Find data dictionary information by querying DICTIONARY with wildcard
-- and grouping the results into USER_/ALL_/DBA_/other.
-- William Robertson, www.williamrobertson.net

break on sort_key skip1 nodup
col sort_key noprint
col table_name format a30
col comments format a78 word

def pattern = &1

with dict_view as
   ( select case
               when substr(table_name,1,5) = 'USER_' then 1
               when substr(table_name,1,4) = 'ALL_' then 2
               when substr(table_name,1,4) = 'DBA_' then 3
               else 4
           end as sort_key
         , table_name
         , comments
    from   dictionary
    where  table_name like upper('%&pattern%')
    union
    select case
               when substr(v.view_name,1,5) = 'USER_' then 1
               when substr(v.view_name,1,4) = 'ALL_' then 2
               when substr(v.view_name,1,4) = 'DBA_' then 3
               else 4
           end as sort_key
         , nvl(s.synonym_name,v.view_name)
         , nvl(d.comments,c.comments)
    from   dba_views  v
           left outer join dictionary d
               on  d.table_name = v.view_name
           left outer join all_synonyms s
               on  s.table_name = v.view_name
               and s.table_owner = v.owner
               and s.owner = 'PUBLIC'
           left outer join dba_tab_comments c
               on  c.table_name = v.view_name
               and c.owner = v.owner
    where  (    v.view_name like upper('V\_$%&pattern%') escape '\'
             or v.view_name like upper('GV\_$&pattern%') escape '\'
             or v.view_name like upper('DBA\_$&pattern%') escape '\' )
    and    d.table_name is null )
select sort_key, table_name, comments
from   ( select sort_key, table_name, comments
              , row_number() over(partition by table_name order by table_name nulls last) as seq
         from   dict_view )
where  seq = 1
order by sort_key, table_name
/