Database Reference
In-Depth Information
CHAPTER 3
■ ■ ■
Introduction to Data Dictionary
Base Tables
E
ach ORACLE database contains a data dictionary that holds metadata, i.e., data about the
database itself. Data dictionary objects are mostly clusters, tables, indexes, and large objects.
The data dictionary is like the engine of a car. If it doesn't ignite (or rather bootstrap using
SYS.BOOTSTRAP$
), then all the other fancy features are quite useless. Traditionally, all data
dictionary objects were stored in the tablespace
SYSTEM
. With the release of Oracle10
g
, the
additional tablespace
SYSAUX
was introduced. This new tablespace contains the Workload
Repository base tables (
WRI$*
and
WRH$*
tables) and other objects.
Knowing how to leverage data dictionary base tables allows a DBA to accomplish tasks
that cannot be completed by accessing data dictionary views built on top of dictionary base
tables. This includes scenarios where dictionary views lack required functionality as well as
workarounds for defects in data dictionary views.
The data dictionary is created behind the scenes when the SQL statement
CREATE
DATABASE
is executed. It is created by running the script
$ORACLE_HOME/rdbms/admin/sql.bsq
. Except for
some placeholders,
sql.bsq
is a regular SQL*Plus script. Oracle9
i
contains 341 data dictionary
base tables, Oracle10
g
712, and Oracle11
g
839.
Database administrators and users seldom access the data dictionary base tables directly.
Since the base tables are normalized and often rather cryptic, the data dictionary views with
prefixes
DBA_*
,
ALL_*
and
USER_*
are provided for convenient access to database metadata.
Some data dictionary views do not have one of these three prefixes (e.g.,
AUDIT_ACTIONS
). The
well-known script
catalog.sql
creates data dictionary views. By looking at view definitions in
catalog.sql
, it becomes apparent which base table column corresponds to which dictionary
view column.
For optimum performance, data dictionary metadata are buffered in the dictionary cache.
To further corroborate the saying that well-designed ORACLE DBMS features have more than
a single name, the dictionary cache is also known as the row cache. The term row cache stems
from the fact that this cache contains individual rows instead of entire blocks like the buffer
cache does. Both caches are in the SGA. The dictionary cache is part of the shared pool, to be
precise.
The role DBA includes read-only access to data dictionary base tables through the system
privilege
SELECT
ANY
DICTIONARY
. This privilege should not be granted frivolously to non-DBA
users. This is especially true for Oracle9
i
where the dictionary base table
SYS.LINK$
contains
unencrypted passwords of database links, whereas the dictionary view
DBA_DB_LINKS
, which is
accessible through the role
SELECT_CATALOG_ROLE
, hides the passwords. Passwords for database
41
