|
The Master Key to Oracle's Data Dictionaryby Jonathan Gennick, author of Oracle SQL*Plus Pocket Reference, 2nd Edition10/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.
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 |
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.
|
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 query ALL_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.
Jonathan Gennick is an O'Reilly Media, Inc. editor specializing in database and programming titles.
|
Related Reading Oracle SQL Plus Pocket Reference |
Return to the O'Reilly Network.
Copyright © 2007 O'Reilly Media, Inc.