 |
|
|
|
 |
|
The Oracle Data Dictionary |
|
|
Virtually everything you could ever want to know about an Oracle database is available by consulting
the data dictionary. In this chapter we first consider the data dictionary from a conceptual perspective
and then we dive into its structure and contents. We conclude with a discussion of whether you can
utilize the data dictionary while programming in the Oracle environment.
If you have used another relational database management system, you’ll find that the conceptual
description of a data dictionary is probably already familiar to you. If, on the other hand, your
programming experience has been with file systems, you may find that the conceptual discussion
is new and, we hope, eye-opening.
What Is the Data Dictionary?
Every programming environment has two basic components: program logic—the instructions of what
the program needs to do—and data. Data can be held temporarily in variables within the program or
persistently in an external store, such as a file or a database. Traditional file structures require the
programmer to understand and then to describe the structure of the file so that the program can properly
access and manipulate the fields and records of the file. Within the Oracle relational database, however,
the structure of the entire database (including its tables, views, indexes, users, security rules, etc.) is stored
within a special set of tables and views known as the data dictionary.
The Oracle data dictionary is owned by a special user account known as SYS. The SYS account is
equivalent to the root superuser of a Unix system. Generally, programmers will never need to connect
to a shared database as this privileged account. Even database administrators are discouraged
from using the SYS account for any but the few actions that require this level of power. If you are
serving as both developer and DBA, perhaps on your personal copy of the Oracle database, you
need to understand the potential risks of doing interactive work while connected as SYS.
Another general item of interest about the Oracle data dictionary is that its tables are stored within
a few special tablespaces (such as SYSTEM and SYSAUX). No other application database objects
should ever be created in these special tablespaces.
The key to the Oracle data dictionary is that it can be accessed using the same SQL language used to
access the application’s data structures within the database. Generally, direct access to the dictionary is
limited to SELECT statements (you will never issue INSERT, UPDATE, or DELETE statements against dictionary
objects). The data dictionary is instead modified indirectly through special SQL statements, known
generally as data manipulation language (DML) commands. DML statements, such as CREATE TABLE,
DROP INDEX, GRANT, and so on, require special permissions. They are generally performed by a DBA in
development organizations that divide responsibilities between DBAs and developers. Student or developers
working independently will have to learn the SQL DML commands in order to build and maintain
their database environment.Structure of the Oracle Data Dictionary
The Oracle data dictionary has multiple levels. The level that you will interact with is a series of views
(owned by the SYS account) that are designed to be easy to use. (Some might question that statement,
but it is apparent once these views are compared to the underlying tables of the dictionary!) These
dictionary views and their columns are all clearly named to represent their contents. Almost all of our
direct interactions with the data dictionary will use these views.
Historically all of the data dictionary views and their underlying tables were owned by the SYS
account. This is still generally true, but as new functionality has been added to the Oracle RDBMS,
some dictionary objects have been created in some other special schemas, such as CTXSYS.
These dictionary views are defined on a series of underlying tables that can be, to say the least, cryptic. These
tables were designed only for use by the developers at Oracle who maintain the database management system.
Before banishing these from our consciousness forever, we’ll look at one example. The upcoming queries
demonstrate several ways of retrieving information about tables in the database. To do so, you need to query
dictionary views such as USER_TABLES. Here’s how USER_VIEWS is defined within the data dictionary:
select o.name, v.textlength, v.text, t.typetextlength, t.typetext,
t.oidtextlength, t.oidtext, t.typeowner, t.typename,
decode(bitand(v.property, 134217728), 134217728,
(select sv.name from superobj$ h, obj$ sv
where h.subobj# = o.obj# and h.superobj# = sv.obj#), null)
from sys.obj$ o, sys.view$ v, sys.typed_view$ t
where o.obj# = v.obj#
and o.obj# = t.obj#(+)
and o.owner# = userenv(‘SCHEMAID’)
For this example we looked at USER_VIEWS, rather than USER_TABLES, because this view definition is only
nine lines long—USER_TABLES is nearly 80 lines of equally unintelligible references to the data dictionary’s
underlying tables. From this one example, it is easy to see why the more friendly views are provided! Let’s
put that unpleasantness aside and return to learning about the more useful dictionary views. When a query is
issued against USER_TABLES, it gets translated via the complex, 80-line view definition and then presented in
a form that you can easily interpret.
Although it isn’t a firm rule, the dictionary views can be considered to fall into five categories:1. The first is the set of views that describe the objects directly owned by the user issuing the
query. The names of these views are prefixed by USER_. There are a few USER_ views that don’t
show owned objects because they reference objects that relate to the entire database rather than
a single user’s schema. USER_USERS, for example, gives you some basic information about other
users of the database.
2. Views in the second set are prefixed by ALL_ and display information about all of the objects
included in the USER_ views as well as those accessible to the user based on permissions
granted from other user accounts.
3. Another set of views provide a way of seeing information about all of the objects in the database,
regardless of owner. These views are intended for use by the database administrators and are
therefore prefixes with DBA_.
4. The fourth set of dictionary views are provided for compatibility with ANSI standardized dictionary
access. Most Oracle developers and administrators use the first three sets of views and ignore this
fourth grouping, which provides less information. You should be aware of them, however, if you are
working with multiple RDBMSs and need a consistent access to their dictionaries.
5. The final set of data dictionary objects are not intended for direct access by most users or common
programs. Over 1,000 of these views (called fixed views) have names beginning with V$ or GV$. (The
V$ views provide information about the instance you are connected to while the GV$ views provide
a global view of all instances within a Real Application Clusters database environment.) Monitoring
and tuning tools as well as SQL scripts developed by system DBAs will commonly select from these
V$ and GV$ views. Developers without access to these tools may find useful tuning information in
some of these views such as V$SQLAREA and V$SYSSTAT. (Access to these views is, by default,
restricted so developers will generally have to be granted the SELECT ANY DICTIONARY system privilege
by a DBA before they will be able to view this information.) Another set of a few hundred special
dictionary views, with names that start with V_$, are even more rarely used directly, even by the
DBAs. Rather than being defined over physical tables within the data dictionary, these views are
constructed on top of various dynamic “tables” that are not actually tables at all. These structures are
named with an X$ prefix and are mappings of memory structures within the Oracle SGA. They are
exposed as X$ tables in order to allow access vian SQL SELECT statements. This allows SQL to be
used as a single, consistent interface to all structures, both user-constructed and system-defined,
within the Oracle database.
Before you look at how a developer might take advantage of the information contained within the data
dictionary, here’s a quick look at an example from each of these view categories. A simple SELECT will
be issued against each to see the differences in the rows returned and descriptive information available.
For simplicity, we won’t show all of the columns available in these views, just a few key columns to
show that each of the views gives a different subset of the total information available.SapereOnline |
|
|
|
|
|
|
|
ORACLE
|
|
|
|
|
| |
|
 |
|
|
|
|
