10.3. Creating and Using Views
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.
10.3.1. 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:
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 Executive103 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.
10.3.2. 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 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 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 UNIQUENESS ------------------------ -------------------- --------- 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_SEQ 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 ShorthandBecause 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:
|
10.3.3. 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.
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 are 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 -------------- --------- ---------- --------- ---------- ------ rac0 dev 10.1.0.2.0 02-JUN-04 12 OPEN 1 row selected.