7 The Data Dictionary

How the Data Dictionary Is Used

The data dictio nary has three primary uses:

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.

How Oracle Uses the Data Dictionary

Data in the base tables of the data dict ionary is necessary for Oracle to function. Therefore, only Oracle should write or change data dictionary i nformation. Oracle provides scripts to modify the data dictionary tables when a database is upgraded or downgraded.

Caution:

No data in any data dictionary ta ble should be altered or deleted by any user.

During data base operation, Oracle reads the data dictionary to ascertain that schema objects exist and that users have proper access to them. Or acle 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.

Public Synonyms for Data Dictionary Views

Oracle creates public synonyms for many data dictionary views so users can access them convenientl y. The security administrator can also create additional public synonyms for schema objects that are used systemwide. Users should av oid naming their own schema objects with the same names as those used for public synonyms.

Cache the Data Dic tionary for Fast Access

Much of the data dictionary information is kept in the SGA in t he dictionary cache, because Oracle constantly accesses the data dictionary during database operation to validate us er access and to verify the state of schema objects. All information is stored in memory using the least recently used (LRU) algorith m.

Parsing information is typically kept in the caches. The COMMENTS columns describing the tables and their colu mns are not cached unless they are accessed frequently.

Other Programs and the Data Dictionary

Other Oracle products can reference existing views and create additional data dictionary ta bles or views of their own. Application developers who write programs that refer to the data dictionary should refer to the public sy nonyms rather than the underlying tables: the synonyms are less likely to change between software releases.

How to Use the Data Dictionary

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 a re intended for database administrators only.

The data dictionary is always available when the database is open. It resides in the SYSTEM tablespace, which is alway s online.

The data dictionary consists of se ts of views. In many cases, a set consists of three views containing similar information and distinguished from each other by their p refixes:

Table 7-1 Data Dictionary View Prefixes

Prefix	Scope
`USER`	User's view (what is in the user's schema)
`AL L`	Expanded user's view (what the user can access)
`DBA`	Databas e administrator's view (what is in all users' schemas)

The set o f columns is identical across views, with these exceptions:

Views with the prefix USER usua lly 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.

See Also:
Oracle Database Reference for a complete list of data dictionary views and their column s

Views with the Prefix USER

The views most likely to be of interest to typical database users ar e those with the prefix USER. These views:

Refer to the user's own private environment in t he 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 t he column OWNER is implied
Return a subset of the information in the ALL views
Can have abbreviated PUBLIC synonyms for convenience

For example, the fol lowing query returns all the objects contained in your schema:

SELECT object_name, object_type FROM USE
R_OBJECTS;

Views with the Prefix ALL

Views with the prefix ALL refer to the user's overall perspective of the database. These v iews 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

Views with the prefix DBA show a global view of the entire database. Synonyms are not cr eated 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 pr
otection to prevent users having the ANY system privileges from using such privileges on the data dictionary. If you ena
ble dictionary protection (O7_DICTIONARY_ACCESSIBILITY is 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 SYSDBA.








Se
e Also:
Oracle Database Administrator's Guid
e for detailed information on system privileges restrictions



 
The DUAL Table
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 o
ne row containing the value X.







See Also:
Oracle Database SQL Reference
 for more information about the DUAL table







 
 
Dynamic Per
formance Tables
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 a
dministrators can query and create views on the tables and grant access to those views to other users. These views are sometimes call
ed fixed views because they cannot be altered or removed by the database administrator.
SYS owns the dynamic performance tables; their names all be
gin with V_$. Views are created on these tables, and then public synonyms are created for the views. The synonym names b
egin 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



 
 
Database Object Metadata
The DBMS_METADATA package provides interfaces for extracting c
omplete definitions of database objects. The definitions can be expressed either as XML or as SQL DDL. Two styles of interface are pr
ovided:


A flexible, sophisticated interface for programmatic control

A simp
lified interface for ad hoc querying






See Also:

PL/SQL Packages and Types Reference for more inf
ormation about DBMS_METADATA

Structure of the Data Dictionary

Base Tables

User-Accessible Views

SYS, Owner of the Data Dictionary