How to create and use views - Oracle DBA

In this section, we'll talk about views that users can create themselves, and then we'll cover views owned by SYS that contain important information about the objects in the database.

User-Defined Views

Views are database objects that look like tables but are instead derived from a SELECT statement performed on one or more tables. In other words, a view is a subset of data from one or more tables. A view does not contain its own data; the contents of a view are dynamically retrieved from the tables on which it is based. A view is sometimes referred to as a stored query.

View A database object that is based on a SELECT statement against one or more tables or other views in the database. A regular view does not store any data in the database; only the definition is stored. Views are also known as stored queries.

Views can enhance the usability of the database by making complex queries appear to be simple. For example, users may frequently join together two or more tables in the same way. A view will make the users' lives a bit easier, allowing them to write a query against a single view instead of needing to rewrite a complex query over and over.

Views can also be used to restrict access to certain rows or columns of a table. For example, the DBA can create a view against the EMPLOYEES table that excludes the SALARY column and can make this view available to those departments that need to see employee information but should not see salary information.

The CREATE VIEW statement looks like this:

The subquery clause is a SELECT statement that may join more than one table and may also have a WHERE clause. Column aliases can be specified for the resulting columns from the subquery.

After reviewing some of the SELECT statements that the users are writing, Janice, the DBA and application developer, notices that there are frequent joins between the EMPLOYEES table and the DEPARTMENTS table, similar to the following:

Creating a view based on this query might help the users who typically don't use SQL to join tables but need to see the associated department information for each employee. Janice creates the view using the sample query above as the sub-query in a CREATE VIEW statement:

Notice that Janice has supplied column aliases so that the original column names are not visible to the users of the view. For all intents and purposes, the EMP_DEPT view looks and operates in the same way as a single table, as demonstrated below with the DESCRIBE and SELECTstatements:

view

view
106 rows selected.

The EMP_DEPT view can be used in the same way as any database table. The users can add a WHERE clause to the SELECT statement above. Also, the EMP_DEPT view can be joined with a table in another query if so desired.

Data Dictionary Views

Data dictionary views are predefined views that contain a variety of information about tables, users, and various other objects in the database. Like other views, data dictionary views are based on one or more tables. The main differences between data dictionary views and user-created views are that data dictionary views are owned by the user SYS and the views themselves may appear to have different results depending on who is accessing them.

Data Dictionary Views Read-only views owned by the user SYS that are created when the database is created and contain information about users, security, and database structures, as well as other persistent information about the database.

Data Dictionary View Types

Data dictionary views have one of three prefixes:

USER_ These views show information about the structures owned by the user (in the user (in the user's schema). They are accessible to all users and do not have an OWNER column.

ALL_ These views show information about all objects that the user has access to, including objects owned by the user and objects to which other users have granted the user access. These views are accessible to all users. Each view has an OWNER column, since some of the objects may reside in other user's schemas.

DBA_ These views have information about all structures in the database—they show what is in all users' schemas. Accessible to the DBA, they provide information on all the objects in the database and have an OWNER column as well.

Common Data Dictionary Views

Some data dictionary views are commonly used by both developers and DBAs to retrieve information about tables, table columns, indexes, and other objects in the database. The following descriptions refer to the ALL_ version of each of the views.

ALL_TABLES

The ALL_TABLES view contains information about all database tables to which the user has access. The following query, run by the user HR, identifies the table and owner of all tables that HR can access:

all-tables

44 rows selected.

Many of the tables visible to HR are tables owned by SYS and SYSTEM, such as the DUAL table. The user HR can also access the EMP and SALGRADE tables owned by SCOTT.

ALL_TAB_COLUMNS

The ALL_TAB_COLUMNS view contains information about the columns in all tables accessible to the user. If the user HR wanted to find out the columns and datatypes in the COUNTRIES table, the query would be written as follows:

ALL_TAB_COLUMNS

3 rows selected.

All_INDEXES

The ALL_INDEXES view contains information about the indexes accessible to the user. If the HR user wanted to find out the indexes that were created against the COUNTRIES table and whether the indexes were unique, the query would look like this:

All_INDEXES

2 rows selected.

The COUNTRIES table has two indexes, one of which is a unique index.

ALL_IND_COLUMNS

The ALL_IND_COLUMNS view contains information about the columns indexed by an index on a table. Following the previous example, the HR user can use the INDEX_NAME to help identify the indexed column or columns on the table:

ALL_IND_COLUMNS

1 row selected.

The index COUNTRY_C_ID_PK indexes the COUNTRY_ID column in the COUNTRIES table.

ALL_OBJECTS

The ALL_OBJECTS view combines all types of Oracle structures into one view. This view comes in handy when you want a summary of all database objects using one query, or you have the name of the object and want to find out what kind of object it is. The following query retrieves all the objects accessible to HR and owned by either the HR or JANICE schema:

ALL_OBJECTS

43 rows selected.

The TEMPORARY (T) column in the ALL_OBJECTS view indicates whether the object is temporary. The temporary table TRAVEL_DEST, created and owned by JANICE but accessible to all users, is indicated correctly as being a temporary table in the query results.

Dynamic performance views

Dynamic performance views are similar in nature to data dictionary views, with one important difference: Dynamic performance views are continuously updated while the database is open and in use; they are re-created when the database is shut down and restarted. In other words, the contents of these views are not retained when the database is restarted. The contents of dynamic performance views primarily relate to the performance of the database.

The names of the dynamic performance views begin with V$. Two common dynamic performance views are V$SESSION and V$INSTANCE.

Dynamic Performance Views Data dictionary views owned by the user SYS that are continuously updated while a database is open and in use and whose contents relate primarily to performance. These views have the prefix V$ and their contents are lost when the database is shut down.

V$SESION

The dynamic performance view V$SESSION contains information about each connected user or process in the database. To find out what programs the user HR is using to connect to the database, you can query the PROGRAM column of V$SESSION:

V$SESION

4 rows selected.

In this case, the user HR has four connections open in the database using three different programs. The SID and SERIAL# columns together uniquely identify a session. This information is needed by the DBA if, for some reason, one of the sessions must be terminated.

V$INSTANCE

The V$INSTANCE view provides one row of statistics for each Oracle instance running against the database. Multiple instances running against a single database can greatly enhance the scalability of the Oracle database by spreading out the CPU resource usage over multiple servers. The following query finds out the version of the Oracle software and how long the instance has been up since the last restart, along with other instance information:

V$INSTANCE

1 row selected.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Oracle DBA Topics