The Master Key to Oracle's Data Dictionaryby Jonathan Gennick, author of Oracle SQL*Plus Pocket Reference, 2nd Edition
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
USERviews return information about objects owned by the currently-logged-on database user. For example, a query to
USER_TABLESreturns a list of all of the relational tables that you own.
ALLviews return information about all objects to which you have access, regardless of who owns them. For example, a query to
ALL_TABLESreturns 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
DBAviews 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_TABLESwill 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
DBAviews. Usually, unless you yourself are a DBA, you won't have access to the
Many views have analogs in all three groups. For example, you have
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
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