Views in SQL

Written by

Carissa O'Connell

October. 10, 2022; Updated: Jan 2023

Views in SQL

A view is “a virtual relation that does not necessarily exist on the database but can be produced upon request by a particular user, at time of request” (Connolly, Begg, pg. 251). The view creates a query that retrieves the data from the database when it is executed, and the result is stored in a temporary table or virtual table and is shown to the user.

What is the difference between Dynamic Performance Views and Static Data Dictionary Views?

A static data dictionary view is not updated automatically and needs to be updated manually whereas the dynamic performance views are updated automatically.

Static Dictionary View

DBA_CREDENTIALS
						

lists all credentials in the database

ALL_CREDENTIALS
					

lists all credentials visible to the user

The scenario where this could be useful is when the owner of the database could be looking to find out which Windows users are authorized to access to the sql server, they could execute one of those commands to find out which usernames have access.

Dynamic Performance View

V$SESSION displays session information for each current session.

This could be useful in a situation when we need to find out what occurred during the session, by who, and when the command was executed.

Reference

Connolly, T. M., & Begg, C. E. (2020). Database systems: A practical approach to design, implementation and management. Pearson India Education Services.