Mastering Oracle Data Dictionaries

Norbert Debes
The following article is an exerpt from Secrets of the Oracle Database, published by APress.

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.

 

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.

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.



Add Comment      Leave a comment on this blog post

Post a comment

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Subscribe to our Newsletters

Sign up now and get the best business technology insights direct to your inbox.


 
Resource centers

Business Intelligence

Business performance information for strategic and operational decision-making

SOA

SOA uses interoperable services grouped around business processes to ease data integration

Data Warehousing

Data warehousing helps companies make sense of their operational data


Close
Thanks for your registration, follow us on our social networks to keep up-to-date