Oracle APEX_DICTIONARY and DBA_DICTIONARY views

Oracle APEX is providing a dictionary view called APEX_DICTIONARY which is returning names and comments for all other dictionary vies.

For example:

SELECT *
FROM apex_dictionary
;

A result:

So, rows with column ID 0 contain a view name and other rows with numbers >= 1 contain column names. 

For Oracle database there is a dictionary view called simply "DICTIONARY" but it returns a limited amount of data. It returns only a list of all dictionary views available without column names and comments. Example:

SELECT *
FROM dictionary
ORDER BY table_name
;

A result:

So, I wanted to extend a functionality of "DICTIONARY" database view in order to collect all information similar to APEX_DICTIONARY view and I wrote a following SELECT statement transformed into a view:

CREATE OR REPLACE VIEW DBA_DICTIONARY AS

SELECT 

    d.table_name,

    atc.column_id,

    atc.column_name,

    com.comments as comments,

    'Column' as comment_type,

    null as parent_view

FROM 

    all_tab_columns atc

    JOIN dictionary d ON atc.table_name = d.table_name AND atc.owner = 'SYS'

    LEFT JOIN all_col_comments com ON atc.table_name = com.table_name AND atc.column_name = com.column_name AND atc.owner = 'SYS'

UNION ALL 

SELECT 

    d.table_name,

    0 as column_id,

    null as column_name,

    com.comments as comments,

    'View' as comment_type,

    null as parent_view

FROM 

    dictionary d

    LEFT JOIN all_tab_comments com ON d.table_name = com.table_name AND com.owner = 'SYS'

ORDER BY 

    table_name,

    column_id

;

A result:

SELECT *

FROM DBA_DICTIONARY

;

If You like it feel free to use it :)