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.)

** If you want the Full Table detail. Refer the SQL Table in Label List. Or Click here to View the Table