The Master Key | Exploring Your Database

It turns out that Oracle's data dictionary is 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. To find the views giving information about a particular class of database object, I find it helpful to perform a wild-card search on the dictionary view's table_name column. Example 10-16 shows this approach being used to list views having to do with stored sequence generators.

Example 10-16. Looking for data dictionary views describing sequences

COLUMN table_name FORMAT A20

COLUMN comments FORMAT A50 WORD_WRAPPED



SELECT table_name, comments

FROM dictionary

WHERE table_name LIKE '%SEQUENCE%';



TABLE_NAME COMMENTS

-------------------- --------------------------------------------------

USER_SEQUENCES Description of the user's own SEQUENCEs

ALL_SEQUENCES Description of SEQUENCEs accessible to the user

DBA_SEQUENCES Description of all SEQUENCEs in the database

The technique used in Example 10-16 is to search for view names containing the word "SEQUENCE".

Views aren't always named the way you think. The view describing a table's columns is dba_tab_columns ; the word table has been abbreviated to tab . If you're interested in information on Oracle object types, you'll find that many views with names containing the word object have nothing whatsoever to do with object types.

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. Example 10-17 retrieves descriptions for the columns in All_sequences .

Example 10-17. Describing the columns in the all_sequences data dictionary view

COLUMN column_name FORMAT A30

COLUMN comments FORMAT A40 WORD_WRAP



SELECT column_name, comments

FROM dict_columns

WHERE table_name = 'ALL_SEQUENCES';



COLUMN_NAME COMMENTS

------------------------------ ----------------------------------------

SEQUENCE_OWNER Name of the owner of the sequence

SEQUENCE_NAME SEQUENCE name

MIN_VALUE Minimum value of the sequence

MAX_VALUE Maximum value of the sequence

INCREMENT_BY Value by which sequence is incremented

CYCLE_FLAG Does sequence wrap around on reaching

 limit?



ORDER_FLAG Are sequence numbers generated in order?

CACHE_SIZE Number of sequence numbers to cache

LAST_NUMBER Last sequence number written to disk

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 . Some relationships are hard to spot. Sometimes it takes a bit of experimentation and research to be certain you have correctly identified the relationship between two views. The recursive relationship from all_constraints to itself is a good example of the kind of relationship that might not be obvious when you first look at the view.

Introduction to SQL*Plus

Command-Line SQL*Plus