Managing Views

About 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.

Like 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 provide an additional level of security by restricting access to a predetermined set of rows and columns of a table. They also hide data complexity and store complex queries.

Many important views are in the SYS schema. The data dictionary views are called static because they change only when a change is made to the data dictionary, for example, when a new table is created or a user is granted new privileges.

Many data dictionary tables have three corresponding views:

• A DBA_ view displays all relevant information in the entire database. DBA_ views are intended only for administrators.

• An ALL_ view displays all the information accessible to the current user, including information from the current user's schema as well as information from objects in other schemas, if the current user has access to those objects through privileges or roles.

• A USER_ view displays all the information from the schema of the current user. No special privileges are required to query these views.

The columns of the ALL_, DBA_, and USER_ views corresponding to a single data dictionary table are usually nearly identical.

The views that monitor ongoing database activity are called dynamic performance views. The names of dynamic performance views, available only to administrators, start with the characters V$ and are often referred to as the V$ views.

Complete descriptions of the views in the SYS schema are contained in the Oracle Database Reference.

Displaying Views

You can use Enterprise Manager to display the views in a specified schema. You can also display the view definitions.

To display views:

1. In the Administration page, click Views.

   The Views property page appears.

2. In the Schema box, enter the name of a schema. For example, enter SYS. Alternatively, click the flashlight icon adjacent to the Schema box to search for a schema.

3. Leave Object Name blank to search for all views in a schema. Alternatively, enter an object name or click the flashlight icon to search for one.

4. Click Go.

   The views in the SYS schema are displayed. These views enable you to look at data dictionary information or ongoing database activity. The views in the SYS schema are also the source of information for many of the Oracle Enterprise Manager pages.

5. In the results list, select a view and then click View or click the link in the View Name column to display a view definition.

   The View: view_name page appears.

Creating a View: Example

The following example creates a view derived from the hr.employees table, which is part of the sample schema. The view filters all direct reports for the manager King, whose employee_id is 100. In an application scenario, this view adds an additional level of security to the hr.employees table while providing a suitable presentation of relevant information for manager King.

To create a view:

1. In the Administration page, click Views.

   The Views property page appears.

2. Click Create.

   The Create View page appears.

3. Enter the following information:

   • In Name, enter KING_VIEW.

   • In Schema, enter HR.

   • In Query Text, enter the following SQL statement:

     SELECT employee_id, first_name, last_name, email, phone_number, hire_date, 
            job_id, salary, commission_pct, manager_id, department_id FROM   hr.employees 
     WHERE  manager_id = 100
     
     

4. Click OK.

   The Views page appears with a confirmation message. The new view is listed in the results section.

5. Select KING_VIEW and then select View Data from the Actions list.

6. Click Go.

   The View Data for View: HR.KING_VIEW page appears. The selected data in the view appears in the Result section.

Dropping Views

If you no longer need a view, then you can drop it using Enterprise Manager.

In this example, you drop the HR.SKING_VIEW view that you created previously.

To drop a view:

1. In the Administration page, click Views.

   The Views page appears.

2. In the Schema box, enter hr. In Object Name, enter SKING_VIEW.

3. Click Go.

   The results page should display the SKING_VIEW view.

4. Select SKING_VIEW and click Delete.

   A Confirmation page appears.

5. Click Yes to delete the view.

   The Views page displays a confirmation message if the view is successfully deleted.