Archive

Posts Tagged ‘oracle views’

Oracle views – Static Data Dictionary Views & Dynamic Performance Views

November 5th, 2011 1 comment

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 theSYS 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_ view is DBA_TABLESPACES, which contains one row for each tablespace in the database.
  • An 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_ view is 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_ view is USER_TABLES, which contains one row for every table owned by the user.

The columns in the DBA_ALL_, and 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 V$ views.

An example of a V$ view is V$SGA, which returns the current sizes of various System Global Area (SGA) memory components.