Database Reference
In-Depth Information
•
System and object privileges (discussed in the previous
chapter) are stored in special catalog tables, thus facilitating the
management of the security mechanisms of the database.
•
As you will see later in the course (Chapter 22), the system catalog
also facilitates the successful implementation of distributed
database systems.
The rest of this chapter will focus on aspects of the system catalog, as implemented
in Oracle. Note however, that all features of the Oracle catalog may not apply to catalogs
of other DBMS suites; also there may be features of others, not included in the Oracle
catalog. Note also that a comprehensive discussion of the Oracle data dictionary is
beyond the scope and intent of this course; the discussion here is necessarily cursory, but
detailed enough to give you a good appreciation of the subject matter.
The Oracle system catalog contains system tables for various database objects:
Figure
14-1
provides some of the most commonly referenced objects that are facilitated
by the system catalog.
Figure 14-1.
Commonly Referenced Catalog Tables
These tables are automatically maintained by the DBMS in a manner that is
transparent to the user. Oracle allows manipulation of catalog tables only through views.
For each table, three views are often available: the view that has the prefix
DBA
(for all
database objects), a view that has the prefix
USER
(for the objects owned by the
current user), and a view that has the prefix
ALL
(for all objects that are accessible to
the current user). However, there are exceptions to this rule. Additionally, views prefixed
by
V$
are dynamic performance views which can be queried, irrespective of your schema,
provided that you have the appropriate privilege. Finally, views prefixed by
GV$
are global
dynamic views.
14.2 Three Important Catalog Tables
To illustrate the importance of the system catalog, let us focus our attention on three
important catalog tables:
Tables, Tab_Columns
and
Indexes
. We will focus on three
views on these tables:
User_Tables, User_Tab_Columns
and
User_Indexes.
14.2.1 The User_Tables View
This catalog view is based on the underlying table
Tables
(the DB2 equivalent being
Systables
). It contains a row for every base table in the user's schema. When a user
account is created, Oracle creates a schema with the same name as the user name.
