Oracle SQLPlus [Electronic resources] : The Definitive Guide, 2nd Edition نسخه متنی

10.11 The Master Key

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.