Creating and Using Views | Oracle9i DBA JumpStart

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 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:

CREATE VIEW view_name (alias1[, alias2] ...)    AS subquery;

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:

select employee_id, last_name, first_name,        department_id, department_name from employees join departments using(department_id);

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 subquery in a CREATE VIEW statement:

create view       emp_dept(emp_id, lname, fname, dept_id, dname) as select employee_id, last_name, first_name,       department_id, department_name from employees join departments using(department_id); View created.

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 SELECT statements:

describe emp_dept;  Name                               Null?    Type  ---------------------------------- -------- ------------  EMP_ID                             NOT NULL NUMBER(6)  LNAME                              NOT NULL VARCHAR2(25)  FNAME                                       VARCHAR2(20)  DEPT_ID                            NOT NULL NUMBER(4)  DNAME                              NOT NULL VARCHAR2(30) select * from emp_dept;  EMP_ID LNAME         FNAME       DEPT_ID DNAME ------- ------------- ----------- ------- ----------------     100 King          Steven           90 Executive     101 Kochhar       Neena            90 Executive     102 De Haan       Lex              90 Executive     103 Hunold        Alexander        60 IT     104 Ernst         Janice           60 IT     105 Austin        David            60 IT     106 Pataballa     Valli            60 IT     107 Lorentz       Diana            60 IT     108 Greenberg     Nancy           100 Finance     109 Faviet        Daniel          100 Finance     110 Chen          John            100 Finance ...     203 Mavris        Susan            40 Human Resources     204 Baer          Hermann          70 Public Relations     205 Higgins       Shelley         110 Accounting     206 Gietz         William         110 Accounting 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’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 that other users have granted the user access to. These views are accessible to all users. Each view has an OWNER column, since some of the objects may reside in other users’ 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:

select table_name, owner from all_tables; TABLE_NAME                     OWNER ------------------------------ ------ DUAL                           SYS SYSTEM_PRIVILEGE_MAP           SYS TABLE_PRIVILEGE_MAP            SYS STMT_AUDIT_OPTION_MAP          SYS AUDIT_ACTIONS                  SYS ... REGIONS                        HR COUNTRIES                      HR LOCATIONS                      HR DEPARTMENTS                    HR JOBS                           HR EMPLOYEES                      HR JOB_HISTORY                    HR EMP                            SCOTT SALGRADE                       SCOTT EMPLOYEES_DEPARTMENTS          HR EMPLOYEES_SSN                  HR CUST_COMMENTS                  HR EMPTY_CUST_COMMENTS            HR 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:

select column_name, data_type from all_tab_columns where table_name = ‘COUNTRIES’; COLUMN_NAME               DATA_TYPE ------------------------- ------------ COUNTRY_ID                CHAR COUNTRY_NAME              VARCHAR2 REGION_ID                 NUMBER 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:

select table_name, index_name, uniqueness from all_indexes where table_name = ‘COUNTRIES’; TABLE_NAME               INDEX_NAME           UNIQUENES ------------------------ -------------------- --------- COUNTRIES                COUNTRY_C_ID_PK      UNIQUE COUNTRIES                COUNTRIES_IE1        NONUNIQUE 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.

select table_name, column_name from  all_ind_columns where index_name = ‘COUNTRY_C_ID_PK’; TABLE_NAME     COLUMN_NAME -----------    ----------------- COUNTRIES      COUNTRY_ID 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:

select owner, object_name, object_type, temporary    from all_objects    where owner in (‘HR’,’JANICE’); OWNER      OBJECT_NAME                OBJECT_TYPE        T ---------- -------------------------- ------------------ - JANICE     TRAVEL_DEST                TABLE              Y HR         ADD_JOB_HISTORY            PROCEDURE          N HR         COUNTRIES                  TABLE              N HR         COUNTRIES_IE1              INDEX              N HR         COUNTRY_C_ID_PK            INDEX              N HR         CUST_COMMENTS              TABLE              N HR         DEPARTMENTS                TABLE              N HR         DEPARTMENTS_SE             SEQUENCE           N HR         DEPT_ID_PK                 INDEX              N ... HR         PK_EMPL_SSN                INDEX              N HR         REGIONS                    TABLE              N HR         REG_ID_PK                  INDEX              N HR         SECURE_DML                 PROCEDURE          N HR         SECURE_EMPLOYEES           TRIGGER            N HR         UK1_EMPLOYEES              INDEX              N HR         UPDATE_JOB_HISTORY         TRIGGER            N 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.

Data Dictionary View Shorthand

Because of how frequently some of the data dictionary views are used by a typical database user, a number of short synonyms exist for these views. Here are some examples of shortened view names:

TABS is a synonym for USER_TABLES.
IND is a synonym for USER_INDEXES.
OBJ is a synonym for USER_OBJECTS.

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.

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.

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

V$SESSION

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:

select sid, serial#, username, program from v$session where username = ‘HR’;        SID    SERIAL# USERNAME           PROGRAM ---------- ---------- ------------------ ----------------         16       6921 HR                 Toad.exe         19         18 HR                 jrew.exe         20         39 HR                 sqlplusw.exe         21       6932 HR                 Toad.exe 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.

select instance_name, host_name, version,    startup_time, round(sysdate-startup_time) "Days Up",    status from v$instance; INSTANCE_NAME HOST_NAME VERSION   STARTUP_T Days Up STATUS ------------- --------- --------- --------- ------- ------ or92          ATH1800   9.2.0.1.0 13-OCT-02       7 OPEN 1 row selected.