Recipe5.7.Describing the Data Dictionary Views in an Oracle Database

Recipe 5.7. Describing the Data Dictionary Views in an Oracle Database

Problem

You are using Oracle. You can't remember what data dictionary views are available to you, nor can you remember their column definitions. Worse yet, you do not have convenient access to vendor documentation.

Solution

This is an Oracle-specific recipe. Oracle not only maintains a robust set of data dictionary views, but there are even data dictionary views to document the data dictionary views. It's all so wonderfully circular.

Query the view named DICTIONARY to list data dictionary views and their purposes:

 select table_name, comments   from dictionary   order by table_name; TABLE_NAME                     COMMENTS ------------------------------ -------------------------------------------- ALL_ALL_TABLES                 Description of all object and relational                                tables accessible to the user ALL_APPLY                      Details about each apply process that                                dequeues from the queue visible to the                                current user … 

Query DICT_COLUMNS to describe the columns in given a data dictionary view:

 select column_name, comments      from dict_columns  where table_name = 'ALL_TAB_COLUMNS'; COLUMN_NAME                     COMMENTS ------------------------------- -------------------------------------------- OWNER TABLE_NAME                      Table, view or cluster name COLUMN_NAME                     Column name DATA_TYPE                       Datatype of the column DATA_TYPE_MOD                   Datatype modifier of the column DATA_TYPE_OWNER                 Owner of the datatype of the column DATA_LENGTH                     Length of the column in bytes DATA_PRECISION                  Length: decimal digits (NUMBER) or binary                                 digits (FLOAT) 

Discussion

Back in the day when Oracle's documentation set wasn't so freely available on the Web, it was incredibly convenient that Oracle made the DICTIONARY and DICT_ COLUMNS views available. Knowing just those two views, you could bootstrap to learning about all the other views, and from thence to learning about your entire database.

Even today, it's convenient to know about DICTIONARY and DICT_COLUMNS. Often, if you aren't quite certain which view describes a given object type, you can issue a wildcard query to find out. For example, to get a handle on what views might describe tables in your schema:

 select table_name, comments   from dictionary  where table_name LIKE '%TABLE%'  order by table_name; 

This query returns all data dictionary view names that include the term "TABLE". This approach takes advantage of Oracle's fairly consistent data dictionary view naming conventions. Views describing tables are all likely to contain "TABLE" in their name. (Sometimes, as in the case of ALL_TAB_COLUMNS, TABLE is abbreviated TAB.)