Oracle SQLPlus [Electronic resources] : The Definitive Guide, 2nd Edition نسخه متنی

10.2 Oracle's Data Dictionary Views

Oracle
has to keep track of all the tables, views, constraints, indexes,
triggers, and other objects you create. To do that, Oracle needs a
place to store the information. This repository of information about
your database is referred to as the data
dictionary . Whenever you create a new object, such as a
table, Oracle stores all the information about that object in the
data dictionary. Modify the object, and Oracle modifies the data
dictionary. It follows, then, that if you want to know anything about
your database, the data dictionary is the place to go.

10.2.1 What Is the Data Dictionary?

The data dictionary is a set of tables owned by the user SYS. The
structure of these tables ends up being fairly complex, and much of
the information isn't stored in a user-friendly
form. You probably do not want to query these tables directly, and
unless you have been given access to log in as user SYS, you
won't be able to see them anyway. To help you out,
Oracle provides a set of data dictionary views .
These views have names that are easy to remember. The column names
used in the views are also easy to remember and use a consistent
naming convention. Data dictionary views exist for each different
type of schema object, and they present information in an
easy-to-understand form. For example, if you are looking at a date
column, the dba_tab_columns view will tell you it
is of type DATE. The underlying data dictionary table, which happens
to be sys.col$, will tell you the type is 12.

Oracle has a large number of data dictionary views. This chapter
concentrates on the views used to return information about the
structure of a table, its constraints, indexes, columns, triggers,
and security. This is the most common type of information needed by
application developers and other database users. I encourage you to
dig deeper. If you want, or need, to know more, then the
Oracle Database Reference manual is a good place
to start. Look for the section titled
" Static Data Dictionary
Views ," which gives a definitive
description of all the views available, and their columns. Another,
perhaps handier reference is Dave Kreines's
Oracle Data Dictionary Pocket Reference
(O'Reilly).

10.2.2 The View Types: user, all, and dba

You need to be aware of three different types of data dictionary
views. These control the scope of the information you can look at:

user
The
user views show you information only about objects
that you own. There is a user_tables view, for
example, that lists only your tables.

all
The all
views show you information about all objects you are able to access.
Anything you own is included in an all view, as
well as anything owned by other users but to which you have been
granted access.

dba
The
dba views show you
information about all objects. Usually, only DBAs have access to
these views, and they can be considered a superset of the
all views. dba_tables, for
example, will list every single table that exists.

Generally, for any given object type, one view of each type will
exist. It's up to you to choose the view you want to
look at. Table 10-1 shows how this works in terms
of the views discussed in this chapter.

As you delve more deeply into Oracle's data
dictionary, you will occasionally find instances when corresponding
views don't exist in all three categories. When a
view is omitted, it's for security reasons, because
it doesn't make sense in the context of a particular
object, or because it would be redundant. The
dba_scheduler_windows and
all_scheduler_windows (new in Oracle Database
10 g ) views provide a good example of this.
DBMS_SCHEDULER windows aren't
"owned" by users, so a
user view doesn't apply in that
context.

Which view should you use? The user views limit
you to seeing information about objects that you own. If
I'm working interactively, I'll
frequently use the user views to save myself some
typing because I don't need to enter a WHERE clause
to restrict the results to my own objects. When writing scripts, I
want to use the all views to make the scripts more
flexible. It's common, for example, to need to see
the definition for a table owned by another user. The
all views allow this. I save the DBA views for
DBA-related tasks.

The following sections show you how to get information about various
types of schema objects. First, I'll show how to
list the tables you own and how to look at the column definitions for
those tables. Next, you will see how to look at the constraints,
indexes, triggers, synonyms, and security for a table.
You'll learn how to leverage the data dictionary to
automate DBA tasks. Finally, I'll hand you the data
dictionary's
master key .