This chapter contains the following topics:
How much space has been allocated for, and is currently used by, the schema objects
Default values for columns
Integrity constraint information
The names of Oracle users
Privileges and roles each user has been granted
Auditing information, such as who has accessed or updated various schema objects
Other general database information
Not only is the data dictionary central to every Oracle database, it is an important tool for all users, from end users to application designers and database administrators. Use SQL statements to access the data dictionary. Because the data dictionary is read only, you can issue only queries (
SELECT statements) against it's tables and views.
See Also:"Bigfile Tablespaces" for more information about
The underlying tables that store information about the associated database. Only Oracle should write to and read these tables. Users rarely access them directly because they are normalized, and most of the data is stored in a cryptic format.
The views that summarize and display the information stored in the base tables of the data dictionary. These views decode the base table data into useful information, such as user or table names, using joins and
WHERE clauses to simplify the information. Most users are given access to the views rather than the base tables.
The Oracle user
SYS owns all base tables and user-accessible views of the data dictionary. No Oracle user should ever alter (
INSERT) any rows or schema objects contained in the
SYS schema, because such activity can compromise data integrity. The security administrator must keep strict control of this central account.
Altering or manipulating the data in data dictionary tables can permanently and detrimentally affect the operation of a database.
Oracle accesses the data dictionary to find information about users, schema objects, and storage structures.
Oracle modifies the data dictionary every time that a data definition language (DDL) statement is issued.
Any Oracle user can use the data dictionary as a read-only reference for information about the database.
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. Oracle provides scripts to modify the data dictionary tables when a database is upgraded or downgraded.
Caution:No data in any data dictionary table should be altered or deleted by any user.
During database operation, Oracle reads the data dictionary to ascertain that schema 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, then new rows are added to the data dictionary that 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.
Oracle creates public synonyms for many data dictionary views so users can access them conveniently. The security administrator can also create additional public synonyms for schema objects that are used systemwide. Users should avoid naming their own schema objects with the same names as those used for public synonyms.
Much of the data dictionary information is kept in the SGA in the dictionary cache, because Oracle constantly accesses the data dictionary during database operation to validate user access and to verify the state of schema objects. All information is stored in memory using the least recently used (LRU) algorithm.
Parsing information is typically kept in the caches. The
COMMENTS columns describing the tables and their columns are not cached unless they are accessed frequently.
Other Oracle products can reference existing views and create additional data dictionary tables or views of their own. 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.
The views of the data dictionary serve as a reference for all database users. Access the data dictionary views with SQL statements. Some views are accessible to all Oracle users, and others are intended for database administrators only.
User's view (what is in the user's schema)
Expanded user's view (what the user can access)
Database administrator's view (what is in all users' schemas)
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.
DBA views have additional columns containing information useful to the administrator.
See Also:Oracle Database Reference for a complete list of data dictionary views and their columns
Refer to the user's own private environment in the database, including information about schema objects created by the user, grants made by the user, and so on
Display only rows pertinent to the user
Have columns identical to the other views, except that the column
OWNER is implied
Return a subset of the information in the
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 refer to the user's overall perspective of the database. These views return information about schema objects to which the user has access through public or explicit grants of privileges and roles, in addition to schema 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 show a global view of the entire database. Synonyms are not created for these views, because
DBA views should be queried only by administrators. Therefore, to query the
DBA views, administrators must prefix the view name with its owner,
SYS, as in the following:
SELECT owner, object_name, object_type FROM SYS.DBA_OBJECTS;
Oracle recommends that you implement data dictionary protection to prevent users having the
ANY system privileges from using such privileges on the data dictionary. If you enable dictionary protection (
false), then access to objects in the
SYS schema (dictionary objects) is restricted to users with the
SYS schema. These users are
SYS and those who connect as
See Also:Oracle Database Administrator's Guide for detailed information on system privileges restrictions
The table named
DUAL is a small table in the data dictionary that Oracle and user-written programs can reference to guarantee a known result. This table has one column called
DUMMY and one row containing the value
See Also:Oracle Database SQL Reference for more information about the
Dynamic performance tables are not true tables, and they should not be accessed by most users. However, database administrators can query and create views on the tables and grant access to those views to other users. These views are sometimes called fixed views because they cannot be altered or removed by the database administrator.
SYS owns the dynamic performance tables; their names all begin with
V_$. Views are created on these tables, and then public synonyms are created for the views. The synonym names begin with
V$. For example, the
V$DATAFILE view contains information about the database's datafiles, and the
V$FIXED_TABLE view contains information about all of the dynamic performance tables and views in the database.
See Also:Oracle Database Reference for a complete list of the dynamic performance views' synonyms and their columns
DBMS_METADATA package provides interfaces for extracting complete definitions of database objects. The definitions can be expressed either as XML or as SQL DDL. Two styles of interface are provided:
A flexible, sophisticated interface for programmatic control
A simplified interface for ad hoc querying
See Also:Oracle Database PL/SQL Packages and Types Reference for more information about