The Oracle Data Dictionary

Key points to remember about the Oracle data dictionary:

• Located in the system tablespace.
• A collection of tables and views maintained by the RDBMS.
• The Oracle user SYS owns the tables and views in the data dictionary.

The tables and views allow you to query the Relational Database Management System ( RDBMS) in order to obtain information concerning the state of the RDBMS, including:

V$SESSION
V$SQLAREA
V$SYSTAT
DBA_CATALOG
DBA_INDEXES
DBA_VIEWS
DBA_USERS
DBA_DATA_FILES
DBA_SEGMENTS
ALL_EXTENTS
ALL_INDEXES
ALL_VIEWS
ALL_USERS
ALL_DATA_FILES
ALL_SEGMENTS
ALL_EXTENTS
USER_INDEXES
USER_VIEWS
USER_USERS
USER_DATA_FILES
USER_SEGMENTS
USER_EXTENTS

Views that start with "V$" are referred to as dynamic performance views.

Views that start with the "DBA" prefix are owned by the user SYS.

Views that start with the prefix "ALL" keep track of all database objects that an end-user can access.

Views that start with the prefix "USER" only contain information about objects owned by the user.

For example, if the user JHOWARD wants to find out which tables he has created, he would query the view USER_TABLES. He can also choose to use the view ALL_TABLES. For example:

SELECT TABLE_NAME FROM USER_TABLES;

SELECT TABLE_NAME FROM ALL_TABLES WHERE OWNER = 'JHOWARD';

SELECT TABLE_NAME FROM DBA_TABLES WHERE OWNER = 'JHOWARD';

Helpful Data Dictionary Views

The tables and views in the Oracle data dictionary provide the DBA with the information that is needed to manage end-user accounts. The Oracle data dictionary is also the central repository for information concerning database performance. A complete description of all of the tables is in the Oracle7 Server Administrators Guide. The objects in the Oracle data dictionary include:

• Tables and views that are owned by the Oracle user SYS or SYSTEM.
• Data dictionary objects that start with USER are data dictionary objects that the individual user has created. Some of the tables include:

Table	Name Description
USER_CATALOG	Tables, views, synonyms, sequences owned.
USER_CONSTRAINTS	Description of the user's own constraints.
USER_INDEXES	Description of the user's own indexes.
USER-SEQUENCES	Description of the user's own sequences.
USER_SYNONYMS	Description of the user's own synonyms.
USER_TABLES	Description of the user's own tables.
USER_USERS	Information about the current user.
USER_VIEWS	Description of views owned by the user.

• Data dictionary objects that start with ALL are objects that the user has been granted access to.

Table	Name Description
ALL_CATALOG	Tables, views, synonyms, sequences accessible by the user.
ALL_CONSTRAINTS	Constraints on all accessible objects.
ALL_INDEXES	Description indexes accessible by the user.
ALL_SEQUENCES	Description of sequences accessible by the user.
ALL_SYNONYMS	Description synonyms accessible by the user.
ALL_TABLES	Description tables accessible by the user.
ALL_USERS	Information about all users of the database.
ALL_VIEWS	Description of views accessible by the user.

• Data dictionary objects that start with DBA can only be accessed by the users SYS and SYSTEM. All other users must be granted access to the objects by the user SYS.

Table	Name Description
DBA_CATALOG	Tables, views, synonyms, sequences owned by the SYS user.
DBA_CONSTRAINTS	Description of all database constraints.
DBA_INDEXES	Description of all indexes in the database.
DBA_SEQUENCES	Description of all sequences in the database.
DBA_SYNONYMS	Description of all synonyms in the database.
DBA_TABLES	Description of all tables in the database.
DBA_USERS	Information about all users of the database.
DBA_VIEWS	Descripton of all views in the database.
DBA_DATA_FILES	Listing of the location and size of the RDBMS datafiles.
DBA_FREESPACE	Listing of freespace in the various tablespaces.
DBA_EXTENTS	List the number of extents for a segment.
DBA_TABLESPACES	Description of the various tablespaces that make up the database.

The objects in the data dictionary should never be deleted or modified.