The data dictionary is the repository for database metadata, which is a fancy term for data describing the database. When you create a table, your description of that table is considered metadata, and Oracle stores that metadata in its data dictionary. Similarly, Oracle stores the definitions for other objects you create, such as views, PL/SQL packages, triggers, synonyms, indexes, and so forth. The database software uses this metadata to interpret and execute SQL statements, and to properly manage stored data. You can use the metadata as your window into the database. Whether you're a DBA or a developer, you need a way to learn about the objects and data within your database.
Codd's fourth rule for relational database systems states that database metadata must be stored in relational tables just like any other type of data. Oracle exposes database metadata through a large collection of data dictionary views. Does this violate Codd's rule? By no means! Oracle's data dictionary views are all based on tables, but the views provide a much more user-friendly presentation of the metadata. For example, to find out the names of all of the relational tables that you own, you can issue the following query:
SELECT table_name FROM user_tables;
Note the prefix user_ in this example. Oracle divides data dictionary views into the three families, as indicated by the following prefixes:
USER_USER views return information about objects owned by the currently-logged-on database user. For example, a query to USER_TABLES returns a list of all of the relational tables that you own.
ALL_ALL views return information about all objects to which you have access, regardless of who owns them. For example, a query to ALL_TABLES returns a list not only of all of the relational tables that you own, but also of all relational tables to which their owners have specifically granted you access (using theGRANT command).
DBA_DBA views are generally accessible only to database administrators, and return information about all objects in the database, regardless of ownership or access privileges. For example, a query to DBA_TABLES will return a list of all relational tables in the database, whether or not you own them or have been granted access to them. Occasionally, database administrators will grant developers access to DBA views. Usually, unless you yourself are a DBA, you won't have access to the DBA views.
Oracle's data dictionary views are mapped onto underlying base tables, but the views form the primary interface to Oracle's metadata. Unless you have specific reasons to go around the views directly to the underlying base tables, you should use the views. The views return data in a much more understandable format than you'll get from querying the underlying tables. In addition, the views make up the interface that Oracle documents and supports. Using an undocumented interface, i.e. the base tables, is a risky practice.Many views have analogs in all three groups. For example, you have USER_TABLES, ALL_TABLES, andDBA_TABLES. A table is a schema object, and thus owned by a user, hence the need forUSER_TABLES. Table owners can grant specific users access to their tables, hence the need forALL_TABLES. Database administrators need to be aware of all tables in the database, hence the need for DBA_TABLES. In some cases, it doesn't make sense for a view to have an analog in all groups. There is no USER_DIRECTORIES view, for example, because directories are database objects not owned by any one user. However, you will find an ALL_DIRECTORIES view to show you the directories to which you have access, and you will find a DBA_DIRECTORIES view to show the database administrator a list of all directories defined in the database.
The primary source of information on Oracle's many data dictionary views is the Oracle9i Database Reference manual. You can access that manual, and many others, from the Oracle Technology Network (OTN). You have to register with OTN in order to view Oracle's documentation online, but registration is free. If you prefer a hardcopy reference, Oracle In A Nutshell, published by O'Reilly & Associates, is another source of Oracle data dictionary information.
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 queryALL_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.
