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

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.