Oracle7 Server Concepts
How the Data Dictionary Is Used
The data dictionary has two primary uses:
- Oracle accesses the data dictionary every time that a DDL statement is issued.
- Any Oracle user can use the data dictionary as a read-only reference for information about the database.
How Oracle and Other Oracle Products Use the Data Dictionary
Data in the base tables of the data dictionary is necessary for Oracle to function. Therefore, only Oracle should write or change data dictionary information.
During database operation, Oracle reads the data dictionary to ascertain that objects exist and that users have proper access to them. Oracle also updates the data dictionary continuously to reflect changes in database structures, auditing, grants, and data.
For example, if user KATHY creates a table named PARTS, new rows are added to reflect the new table, columns, segment, extents, and the privileges that KATHY has on the table. This new information is then visible the next time the dictionary views are queried.
Caching of the Data Dictionary for Fast Access
Because Oracle constantly accesses the data dictionary during database operation to validate user access and to verify the state of objects, much of the data dictionary information is cached in the SGA. All information is stored in memory using the LRU (least recently used) algorithm. Information typically kept in the caches is that required for parsing. The COMMENTS columns describing the tables and columns are not cached unless they are frequently accessed.
Other Programs and the Data Dictionary
Other Oracle products can create additional data dictionary tables or views of their own and reference existing views. Application developers who write programs that refer to the data dictionary should refer to the public synonyms rather than the underlying tables: the synonyms are less likely to change between software releases.
Adding New Data Dictionary Items
You can add new tables or views to the data dictionary. If you add new data dictionary objects, the owner of the new objects should be the user SYSTEM or a third Oracle user. Never create new objects belonging to user SYS, except by running script provided by Oracle Corporation for creating data dictionary objects.
Deleting Data Dictionary Items
Because all changes to the data dictionary are performed by Oracle in response to DDL statements, no data in any data dictionary tables should be deleted or altered by any user.
The single exception to this rule is the table SYS.AUD$. When auditing is enabled, this table can grow without bound. Although you should not drop the AUDIT_TRAIL table, the security administrator can delete data from it because the rows are for information only and are not necessary for Oracle to run.
Public Synonyms for Data Dictionary Views
Public synonyms are created on many data dictionary views so they can be conveniently accessed by users. The security administrator can create additional public synonyms for objects used systemwide. However, other users should avoid naming their own objects with the same names as those used for public synonyms.
How Oracle Users Can Use the Data Dictionary
The views of the data dictionary serve as a reference for all database users. Access to the data dictionary views is via the SQL language. Certain views are accessible to all Oracle users, while others are intended for administrators only.
The data dictionary is always available when the database is open. It resides in the SYSTEM tablespace, which is always online.
The data dictionary consists of sets of views. In many cases, a set consists of three views containing similar information and distinguished from each other by their prefixes:
| Prefix
| Scope
|
| USER
| user's view (what is in the user's schema)
|
| ALL
| expanded user's view (what the user can access)
|
| DBA
| database administrator's view (what all users can access)
|
Table 8 - 1. Data Dictionary View Prefixes
The set of columns is identical across views with these exceptions:
- Views with the prefix USER usually exclude the column OWNER. This column is implied in the USER views to be the user issuing the query.
- Some DBA views have additional columns containing information useful to the administrator.
Views with the Prefix USER
The views most likely to be of interest to typical database users are those with the prefix USER. These views
- refer to the user's own private environment in the database, including information about objects created by the user, grants made by the user, and so on
- display only rows pertinent to the user
- have identical columns to the other views, except that the column OWNER is implied (the current user)
- return a subset of the information in the ALL_ views
- can have abbreviated PUBLIC synonyms for convenience
For example, the following query returns all the objects contained in your schema:
SELECT object_name, object_type FROM user_objects;
Views with the Prefix ALL
Views with the prefix ALL refer to the user's overall perspective of the database. These views return information about objects to which the user has access via public or explicit grants of privileges and roles, in addition to objects that the user owns. For example, the following query returns information about all the objects to which you have access:
SELECT owner, object_name, object_type FROM all_objects;
Views with the Prefix DBA
Views with the prefix DBA show a global view of the entire database. Therefore, they are meant to be queried only by database administrators. Any user granted the system privilege SELECT ANY TABLE can query the DBA-prefixed views of the data dictionary.
Synonyms are not created for these views, as the DBA views should only be queried by administrators. Therefore, to query the DBA views, administrators must prefix the view name with its owner, SYS, as in
SELECT owner, object_name, object_type FROM sys.dba_objects;
Administrators can run the script file DBA_SYNONYMS.SQL to create private synonyms for the DBA views in their accounts if they have the SELECT ANY TABLE system privilege. Executing this script creates synonyms for the current user only.
DUAL
The table named DUAL is a small table that Oracle and user-written programs can reference to guarantee a known result. This table has one column and one row.