Database Reference
In-Depth Information
links are encrypted during the upgrade process to Oracle10
g
. Table 3-1 lists some dictionary
tables that are related to prominent database objects.
Table 3-1.
Data Dictionary Base Tables
Object
Data Dictionary
Base Table
Associated DBA_* View(s)
Clusters
CLU$
DBA_CLUSTERS, DBA_SEGMENTS
Database links
LINK$
DBA_DB_LINKS
Data files
FILE$
DBA_DATA_FILES, DBA_FREE_SPACE
Free extents
FET$
DBA_FREE_SPACE
Indexes
IND$
DBA_INDEXES
Large objects
LOB$
DBA_LOBS
Database objects
OBJ$
DBA_OBJECTS, DBA_LOBS, DBA_TYPES
Segments
SEG$
DBA_SEGMENTS
Tables
TAB$
DBA_TABLES, DBA_LOBS
Tablespaces
TS$
DBA_TABLESPACES, DBA_DATA_FILES, DBA_LOBS
Types
TYPE$
DBA_TYPES
Used extents
UET$
DBA_SEGMENTS, DBA_FREE_SPACE
Users
USER$
DBA_USERS, DBA_DB_LINKS, DBA_LOBS
Of course, dictionary base tables should never be changed directly, as this may easily
cause database corruption. Querying dictionary base tables should be considered when data
dictionary views do not expose enough information to solve a task. Sometimes dictionary
views have bugs, which can be worked around by accessing the base tables directly. The script
sql.bsq
is well commented, such that reading this script may aid in understanding the struc-
ture of the dictionary base tables.
Large Objects and PCTVERSION vs. RETENTION
An example of leveraging direct access to dictionary base tables is an issue with the data dictionary
view
DBA_LOBS
in Oracle9
i
and Oracle10
g
Release 1. The view fails to correctly report the versioning
setting for LOB segments. Since Oracle9
i
, multiversion read consistency for LOBs is done either
by setting aside a certain percentage of storage in the LOB segment (SQL keyword
PCTVERSION
;
old approach) or with undo segments (SQL keyword
RETENTION
; new approach). The default for
an Oracle9
i
database with automatic undo management is
PCTVERSION
. For an Oracle10
g
data-
base in automatic undo management mode, the default is
RETENTION
. The setting of
RETENTION
cannot be specified with SQL syntax and is copied from the parameter
UNDO_RETENTION
. Here's
an example that uses both approaches within a single table:
