7.2 How do I…Determine all of the views owned by a particular user?Problem
We have many views in our organization. I cannot always remember the names of the views to which I have access. Within our decision support applications, I want to present a list of the views a user has available to display data. How do I determine the views owned by a particular user account?
Technique
The USER_VIEWS data dictionary view contains views owned by the user account connected to the database. The ALL_VIEWS data dictionary view contains the views that a user account owns or has been granted privileges on. Either view can be queried to list the views available. Table 7.1 shows the columns available in the ALL_VIEWS data dictionary view.
Table 7.1 ALL_VIEWS data dictionary view
Column Name Description OWNER Owner of the view VIEW_NAME Name of the view TEXT_LENGTH Length of the view’s text TEXT The view’s SELECT statement—stored in a LONG column TEXT_TYPE_LENGTH (Object view)Length of the type clause of the view TYPE_TEXT (Object view)The text of the type clause of the view OID_TEXT_LENGTH (Object view)—The length of the text of the WITH OID clause OID_TEXT (Object view)—The text of the WITH OID clause VIEW_TYPE_OWNER (Object view)—Owner of the typed view VIEW_TYPE (Object view)—The type of view. The columns pertinent to this How-To are the OWNER, VIEW_NAME, and TEXT columns. The OWNER column contains the owner of the view, and the VIEW_NAME column contains the view’s name. The TEXT column contains the query used to generate the records returned by the view.
Steps
1. Run SQL*Plus and connect as the WAITE user account. Load CHP7_5.SQL into the SQL buffer. The file contains a query to list the views available to the user account.
SQL> GET CHP7_5.sql
1 SELECT OWNER||’.’||VIEW_NAME
2 FROM
3* ALL_VIEWS
Line 1 concatenates the owner of the view with the name of the view to present the output in the owner.objectname format.
2. Run the statement to list the views. Since the WAITE user account was granted the DBA role by the installation script, every view in the database is listed. A portion of the results of the query are shown in Figure 7.3.
How It Works
The ALL_VIEWS data dictionary view contains information about the views to which a user account has access. Steps 1 and 2 query the ALL_VIEWS data dictionary view to list all the views to which the WAITE user account has been granted access. The OWNER and VIEW_NAME columns are concatenated to return a single column. If you want to list only the views owned by the connected user account, use the USER_VIEWS data dictionary view.
Comments
Views can be used in many places, just like tables. Retrieving a list of available views is a fairly easy process. If you are developing an application that needs to include a list of view names, you can include the data dictionary views in your application.