Oracle views – Static Data Dictionary Views & Dynamic Performance Views
Views are customized presentations of data in one or more tables or other views. You can think of them as stored queries. Views do not actually contain data, but instead derive their data from the tables upon which they are based. These tables are referred to as the base tables of the view.
Similar to tables, views can be queried, updated, inserted into, and deleted from, with some restrictions. All operations performed on a view actually affect the base tables of the view. Views can provide an additional level of security by restricting access to a predetermined set of rows and columns of a table. They can also hide data complexity and store complex queries.
Many important views are in the
SYS schema. There are two types: static data dictionary views and dynamic performance views. Complete descriptions of the views in the
SYS schema are in Oracle Database Reference.
Static Data Dictionary Views
The data dictionary views are called static views because they change infrequently, only when a change is made to the data dictionary. Examples of data dictionary changes include creating a new table or granting a privilege to a user.
Many data dictionary tables have three corresponding views:
DBA_view displays all relevant information in the entire database.
DBA_views are intended only for administrators.An example of a
DBA_TABLESPACES, which contains one row for each tablespace in the database.
ALL_view displays all the information accessible to the current user, including information from the schema of the current user, and information from objects in other schemas, if the current user has access to those objects through privileges or roles.An example of an
ALL_TABLES, which contains one row for every table for which the user has object privileges.
USER_view displays all the information from the schema of the current user. No special privileges are required to query these views.An example of a
USER_TABLES, which contains one row for every table owned by the user.
The columns in the
USER_ views are usually nearly identical.
Dynamic Performance Views
Dynamic performance views monitor ongoing database activity. They are available only to administrators. The names of dynamic performance views start with the characters
V$. For this reason, these views are often referred to as
An example of a
V$ view is
V$SGA, which returns the current sizes of various System Global Area (SGA) memory components.