| Oracle® Database 2 Day DBA 11g Release 1 (11.1) Part Number B28301-01 |
|
|
View PDF |
| Oracle® Database 2 Day DBA 11g Release 1 (11.1) Part Number B28301-01 |
|
|
View PDF |
The following sections describe how to create and manage 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:
A 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.
A 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.
You can use Oracle Enterprise Manager Database Control (Database Control) to list the views in a specified schema. You can also display the view definitions.
To display views:
Go to the Database Home page, logging in as user SYSTEM.
At the top of the page, click Schema to view the Schema subpage.
In the Database Objects section, click Views.
The Views page appears.
In the Schema field, enter the name of a schema. Alternatively, click the flashlight icon adjacent to the Schema field to search for a schema.
Examples of schema names include SYS and hr.
Leave Object Name blank to search for and display all views in the schema. Alternatively, enter a view name or partial view name to limit the search.
If you enter a search string in Object Name, all views that have names that start with the search string are displayed. If you precede the search string with an asterisk (*), all views that have the search string anywhere in the view name are displayed.
Click Go.
The views in the specified schema are displayed.
To view the definition of a particular view, select the view and then click View. Alternatively, double-click the view name.
The View page appears.
See Also:
In this example, you create a view named king_view, which uses the hr.employees table as its base table. (The hr schema is part of the sample schemas.) This view filters the table data so that only direct reports for the manager King, whose employee ID is 100, are returned in queries. 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 the KING_VIEW view on the HR.EMPLOYEES table:
Go to the Database Home page, logging in as user hr or as user SYSTEM.
At the top of the page, click Schema to view the Schema subpage.
In the Database Objects section, click Views.
The Views page appears.
Click Create.
The Create View page appears.
Enter the following information:
In the Name field, enter king_view.
In the Schema field, enter hr.
In the Query Text field, enter the following SQL statement:
SELECT * FROM hr.employees WHERE manager_id = 100
Click OK.
The Views page returns and displays a confirmation message. The new view appears in the list of views.
To test the new KING_VIEW view:
On the Views page, select king_view and then select View Data from the Actions list.
Click Go.
The View Data for View page appears. The data selected by the view appears in the Result section.
(Optional) You can also test the view by submitting the following SQL statement in SQL*Plus or SQL Developer:
SELECT * FROM king_view
See Also:
If you no longer need a view, you can delete it using Database Control.
In this example, you delete the hr.king_view view that you created previously in "Example: Creating a View".
To delete the HR.KING_VIEW view:
Go to the Database Home page, logging in as user SYSTEM.
At the top of the page, click Schema to view the Schema subpage.
In the Database Objects section, click Views.
The Views page appears.
In the Schema field, enter hr.
In the Object Name field, enter king.
You can enter just the first few letters of the view name.
Click Go.
KING_VIEW is displayed in the list of views.
Select KING_VIEW, and then click Delete.
A Confirmation page appears.
Click Yes to delete the view.
The Views page returns and displays a confirmation message.
See Also:
