Oracle SQL*Plus: The Definitive Guide, 2nd Edition

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

Warning

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