Managing Database Instances (part 3)
Oracle Database Metadata
An Oracle Database contains two types of metadata views:
- Data dictionary views
- Dynamic performance views
The SYS user owns the data dictionary and dynamic performance views in the Oracle database,
and they are stored in the SYSTEM tablespace. During normal database operation,
Oracle frequently uses the data dictionary and updates the dictionary with the current status of the database components.
The dictionary is also immediately updated when a DDL statement is executed.
A-Data dictionary
Views provide information about the database and its objects.
Depending on which features are installed and configured, an Oracle Database can contain more than 2,000 data dictionary views.
Data dictionary views have names that begin with DBA_, ALL_, and USER_.
For every DBA_ view a corresponding CDB_ view also exists with a container identifier.
Oracle creates public synonyms on many data dictionary views so users can access the views conveniently.
Data dictionary is owned by user SYS.
The difference between the DBA_, ALL_, and USER_ views can be illustrated using the DBA_ OBJECTS data dictionary view as an example.
The DBA_OBJECTS view shows information on all the objects in the database.
The corresponding ALL_OBJECTS view, despite its name, shows only the objects that a particular database user owns or can access.
For example,
if you were logged into the database as a user named SCOTT, the ALL_OBJECTS view would show all the objects owned by the user SCOTT and the objects to which SCOTT has been granted access by other users or through a system privilege.
The USER_OBJECTS view shows only those objects owned by a user. If the user SCOTT were to examine the USER_OBJECTS view, only those objects he owns would be displayed. In a multitenant container database,multiple pluggable databases are present. The CDB_ views are available for the CDB administrator to view information from all the databases in the CDB.
Because the DBA_ views provide the broadest metadata information, they are generally the data dictionary views used by DBAs.
Examples of a few DBA_ data dictionary views.
select * from dictionary;
B-Dynamic Performance Views
Throughout database operation, Oracle updates a set of virtual tables to record the current database activity and status.
These tables are called dynamic performance tables.
Views are created on top of the dynamic performance tables so that information is grouped better and names are in a user-friendly format.
The dynamic performance views are sometimes called fixed views, because they cannot be altered or removed by the database administrator.
The dynamic performance tables begin with X$. The dynamic performance view names begin with V_$.Public synonyms are created for these views, and they begin with V$.
For example, the dynamic performance view with data file information is v_$datafile, whereas the public synonym is v$datafile.
Depending on which features are installed and configured, an Oracle database can contain over 700 dynamic performance views. Most of these views have names that begin with V$.
These tables are called dynamic performance tables.
Views are created on top of the dynamic performance tables so that information is grouped better and names are in a user-friendly format.
The dynamic performance views are sometimes called fixed views, because they cannot be altered or removed by the database administrator.
The dynamic performance tables begin with X$. The dynamic performance view names begin with V_$.Public synonyms are created for these views, and they begin with V$.
For example, the dynamic performance view with data file information is v_$datafile, whereas the public synonym is v$datafile.
Depending on which features are installed and configured, an Oracle database can contain over 700 dynamic performance views. Most of these views have names that begin with V$.
C- Data Dictionary vs. Dynamic Performance Views
A-Data Dictionary ViewsThe DBA_ views usually have plural names (for example, DBA_DATA_FILES).The DBA_ views are available only when the database is open and running.The data contained in the DBA_ views is static and is not cleared when the database is shut down.B-Dynamic Performance ViewsThe names of the V$ views are generally singular (for example, V$DATAFILE).Some V$ views are available even when the database is not fully open and running.The V$ views contain dynamic statistical data that is lost each time the database is shut down.
The data dictionary view DICTIONARY shows information about the data dictionary and dynamic performance views in the database , DICT is a synonym for the DICTIONARY view The COMMENTS column shows the purpose or contents of the view.
The V$FIXED_TABLE view lists the dynamic performance tables and views in the database.
You are not allowed to log in as SYS and modify the data dictionary views or update information directly using SQL . The only SYS-owned table you are allowed to delete records from is AUD$. This table is used to store database audit information.
