Oracle9i Database Concepts Release 1 (9.0.1) Part Number A88856-02 |
|
This chapter describes the central set of read-only reference tables and views of each Oracle database, known collectively as the data dictionary. The chapter includes:
One of the most important parts of an Oracle database is its data dictionary, which is a read-only set of tables that provides information about the database. A data dictionary contains:
The data dictionary is structured in tables and views, just like other database data. All the data dictionary tables and views for a given database are stored in that database's SYSTEM
tablespace.
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. To access the data dictionary, use SQL statements. Because the data dictionary is read-only, you can issue only queries (SELECT
statements) against the tables and views of the data dictionary.
A database's data dictionary consists of:
The Oracle user SYS
owns all base tables and user-accessible views of the data dictionary. No Oracle user should ever alter (update, delete, or 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.
The data dictionary has three primary uses:
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.
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,
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 on 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 cached 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 LRU (least recently used) algorithm.
Information typically kept in the caches is that required for parsing. 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. You access the data dictionary views through the SQL language. Some views are accessible to all Oracle users, and others are intended for database 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'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:
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.
Oracle9i Database Reference for a complete list of data dictionary views and their columns
See Also:
The views most likely to be of interest to typical database users are those with the prefix USER.
These views:
OWNER
is implied (the current user)
ALL
views
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. 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, because the 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;
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.
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 X.
Throughout its operation, Oracle maintains a set of "virtual" tables that record current database activity. These tables are called dynamic performance tables.
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:
Oracle9i Database Reference for a complete list of the dynamic performance views' synonyms and their columns |
Oracle9i, Release 1 (9.0.1), includes a PL/SQL package, DBMS_METADATA,
which 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:
Oracle9i Supplied PL/SQL Packages and Types Reference for more information about
See Also:
DBMS_METADATA
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|