Mastering Oracle Data Dictionaries

Each 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 Oracle10g, 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. Oracle9i contains 341 data dictionary
base tables, Oracle10g 712, and Oracle11g 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 Oracle9i 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 links are encrypted during the upgrade process to Oracle10g.

The following table lists some dictionary tables that are related to prominent database objects.

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.

Apress is a technical publisher devoted to meeting the needs of IT professionals, software developers, and programmers, with more than 700 books in print and a continually expanding portfolio of publications. Apress provides high-quality, no-fluff content in print and electronic formats that help serious technology professionals build a comprehensive pathway to career success.