Accessing Data Dictionary and Dynamic Performance Views

During normal database operation, Oracle accesses data dictionary contents almost continuously. It expects these structures to be as they were created by the Oracle scripts and therefore should never be altered.

To make these views more usable for DBAs and other users, Oracle creates public synonyms for many of the data dictionary views. Public synonyms are aliases created for objects in the database that are available to everyone who connects to the database to use. These public aliases allow anyone who wants to access the object in question to do so without having to prefix the object name with the owner's schema name. Often these synonyms are on global type tables or packages that have broad application and limited security requirements (such as a single predefined calendar table with all the dates from 1900 to 3000 in it). These can be contrasted with private synonyms granted from one schema to another schema individually as the need arises. This allows the views to more easily be accessed by anyone regardless of the way that he is logged in; you don't have to prefix them with the SYS user's prefix. DBAs can also, after considering security implications, create additional public synonyms for objects used systemwide.

Because of the system implications, it is important that users avoid naming their own schema objects with the same name as those used for the public synonyms for data dictionary objects.

To determine what is available in the data dictionary, you can query the DICTIONARY view. After you have found a likely view, you can then DESCRIBE the view to learn the columns associated with that view and then SELECT the information relevant in your situation.

An example of a query that you can run against the DICTIONARY data dictionary view follows. This query can provide you with an extensive listing of what is in the data dictionary and the kind of information that can be found, and where.

 COLUMN table_name FORMAT A30 COLUMN COMMENTS FORMAT A45 WORD_WRAP SELECT table_name, COMMENTS FROM DICTIONARY; 

A representative sample of the output of this query appears in Table 5.1.

Table 5.1. Data Dictionary Views

Table Name	Comments
DBA_TAB_MODIFICATIONS	Information regarding modifications to tables.
DBA_TAB_PRIVS	All grants on objects in the database.
DBA_SYNONYMS	All synonyms in the database.
DBA_SYS_PRIVS	System privileges granted to users and roles.
DBA_RGROUP	All refresh groups. This view is not a join.
DBA_ROLES	All roles that exist in the database.
DBA_ROLE_PRIVS	Roles granted directly to users or granted to other roles.
DBA_ROLLBACK_SEGS	Description of rollback segments.
DBA_RSRC_CONSUMER_GROUPS	All the resource consumer groups.
DBA_RSRC_CONSUMER_GROUP_PRIVS	Switch privileges for consumer groups.
DBA_RSRC_MANAGER_SYSTEM_PRIVS	System privileges for the resource manager.
DBA_RSRC_PLANS	All the resource plans.
DBA_RSRC_PLAN_DIRECTIVES	All the resource plan directives.
DBA_RULES	Rules in the database.

If you need more information about what a particular column means than you can get from just describing the view, the Comments column in Table 5.1 can provide insight into what each column's contents mean.

There are synonyms for the DICTIONARY view. CAT, CATALOG, and DICT are all other names referring to the same view, and all can be used interchangeably. This may occur on the exam as a means to trip you up.

Any Oracle product can reference existing views and can (and typically do) create additional data dictionary views of their own.

Application programs, written by in-house or contracted developers who write programs that reference the data dictionary for any reason, should always refer to the public synonyms and never to the underlying tables. This is not only safer for the Oracle software, it also is safer for the accessing programs because the public synonyms' contents are less likely to change from release to release, whereas the underlying tables are more apt to.

Some of the views are designed to be accessible by all users; others by database administrators only. The prefix of the view (for example, DBA_, USER_, or ALL_) usually provides a clue to which views are designed for whom.

Fast Access of the Data Dictionary

Much of the data dictionary information is kept in the SGA in the data dictionary cache. This is done because Oracle constantly accesses the data dictionary during operation. The information stored in the data dictionary cache is aged out using the least recently used algorithm.

Parsing information is almost continuously found in the data dictionary cache. Comments describing the tables and their contents are not cached unless they are accessed frequently enough to qualify them as cacheable.