The Master Key to Oracle's Data Dictionary
Pages: 1, 2

The Self-Documenting Dictionary

In addition to the sources of information just cited, Oracle's data dictionary is itself self-documenting. When working with a properly created Oracle database, you can query the DICTIONARY and DICT_COLUMNS views for descriptions of the data dictionary views and their columns, a sort of meta-metadata. I refer to these two views as the master key to Oracle's data dictionary. The following example shows how you can query the DICTIONARY view from SQL*Plus, Oracle's standard command-line client, to get descriptions of all of the data dictionary views:

SQL> COLUMN table_name FORMAT A30
SQL> COLUMN comments FORMAT A45 WORD_WRAP
SQL> SELECT table_name, comments
 2 FROM dictionary
 3 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

ALL_APPLY_CONFLICT_COLUMNS     Details about conflict resolution on tables

                               visible to the current user
...

An unqualified query against DICTIONARY will return a large amount of output. By using a WHERE clause, you can focus on a smaller set of views. The following query returns all views containing the word TABLE:

SELECT table_name, comments
FROM dictionary
WHERE table_name LIKE '%TABLE%'
ORDER BY table_name

Be aware that views aren't always named the way you might think. The view describing a table's columns is DBA_TAB_COLUMNS; the word TABLE has been abbreviated to TAB.

Once you've isolated a view of interest, you can query the DICT_COLUMNS view for a description of the data returned by the columns that make up the view. The following example retrieves descriptions for the columns in ALL_TAB_COLUMNS:

SQL> COLUMN column_name FORMAT A30
SQL> COLUMN comments FORMAT A45 WORD_WRAP
SQL> SELECT column_name, comments

 2 FROM dict_columns
 3 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)

Data dictionary views are often interrelated, and these relationships are generally quite apparent from the column names. Look at ALL_TABLES and ALL_TAB_COLUMNS, and you'll see that you can join those two views on OWNER and TABLE_NAME. Thus, once you've used ALL_TABLE to identify a table of interest, you can use the corresponding TABLE_NAME and OWNER values from ALL_TABLE to query ALL_TAB_COLUMNS in order to retrieve the column definitions for the table.

Sometimes it takes a bit of experimenting and research to be certain you have correctly identified the relationship between two views.

You can learn a lot about Oracle from the data dictionary views. They are also your primary source of information about objects you create in the database. If you do a lot of work with Oracle, it pays to become familiar with these views because, while GUI tools to retrieve and display metadata are in fact available, they are not universally available at all Oracle sites.

Jonathan Gennick is an O'Reilly Media, Inc. editor specializing in database and programming titles.

Related Reading Oracle SQL Plus Pocket Reference A Guide to SQL*Plus Syntax By Jonathan Gennick

Return to the O'Reilly Network.

What's your favorite view in the data dictionary?
You must be logged in to the O'Reilly Network to post a talkback.

Showing messages 1 through 4 of 4.

• Corrections (Editor's Note)
  2002-10-29 10:08:46  chromatic | [Reply | View]
  
  
  A previous version of the article incorrectly suggested that the views show all tables. Per the author's suggestion, I've changed the article to state that the views show all relational tables. I apologize for the inconvenience.
• Corrections (Editor's Note)
  2002-10-29 12:20:46  Jonathan Gennick | [Reply | View]
  
  
  XML tables, and object tables in general, are a good example of what won't show up in USER_TABLES. It used to be that USER_TABLES really did show all tables you owned, but as Oracle's gotten more complicated and added more types of tables, that's no longer true.

• Most powerful views...
  2002-10-29 01:28:05  chrisrimmer [Reply | View]
  
  
  I like the *_SOURCE views. Having all of the source code of packages available is very powerful. It lets you do things like dynamically recompile packages on the fly to emulate Java Mock Objects for testing, or easily check for references to a given package or procedure call.
  
  But then I'm a developer not a DBA, so I guess I'm bound to find the code objects more interesting!
• Most powerful views...
  2002-11-06 11:53:38  neh123us@yahoo.com [Reply | View]
  
  
  Additional very powerful views are *_errors which will outline details about the compile time errors for a package or stored proedure. If you have a tool that doesn't supply nice error messages, you can always query this.
  
  All_arguments outlines the paramaters in each of the procedures/functions in a package.
  
  You can easily query this to produce wrapper classes to wrap around making calls to the stored procs.