The Master Key to Oracle's Data Dictionary

by Jonathan Gennick, author of Oracle SQL*Plus Pocket Reference, 2nd Edition
10/28/2002

You've just taken on responsibility for a new database. New to you, that is -- the database has actually been running for some years. What's the first thing you want to do? If you're like me, you probably want to get a handle on just what it is that you have. What's in the database? How big is it? Who are the users and how many of them are there? The answers to all of these questions lie in the Oracle data dictionary.

Viewing Database Metadata

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

Related Reading

Oracle in a Nutshell
A Desktop Quick Reference
By Rick Greenwald, David C. Kreines

Many views have analogs in all three groups. For example, you have USER_TABLES, ALL_TABLES, and DBA_TABLES. A table is a schema object, and thus owned by a user, hence the need for USER_TABLES. Table owners can grant specific users access to their tables, hence the need for ALL_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.

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.

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.

Pages: 1, 2