|Oracle9i Database Administrator's Guide
Release 1 (9.0.1)
Part Number A90117-01
This chapter describes the management of views, sequences, and synonyms and contains the following topics:
A view is a tailored presentation of the data contained in one or more tables (or other views), and takes the output of a query and treats it as a table. You can think of a view as a "stored query" or a "virtual table." You can use views in most places where a table can be used.
This section describes aspects of managing views, and contains the following topics:
To create a view, you must meet the following requirements:
CREATE VIEWprivilege. To create a view in another user's schema, you must have the
CREATE ANY VIEWsystem privilege. You can acquire these privileges explicitly or through a role.
INSERTprivilege for Scott's
emptable, the view can only be used to insert new rows into the
emptable, not to
GRANT OPTIONor the system privileges with the
You can create views using the
CREATE VIEW statement. Each view is defined by a query that references tables, materialized views, or other views. As with all subqueries, the query that defines a view cannot contain the
FOR UPDATE clause.
The following statement creates a view on a subset of data in the
CREATE VIEW sales_staff AS SELECT empno, ename, deptno FROM emp WHERE deptno = 10 WITH CHECK OPTION CONSTRAINT sales_staff_cnst;
The query that defines the
sales_staff view references only rows in department 10. Furthermore, the
CHECK OPTION creates the view with the constraint (named
UPDATE statements issued against the view cannot result in rows that the view cannot select. For example, the following
INSERT statement successfully inserts a row into the
emp table by means of the
sales_staff view, which contains all rows with department number 10:
However, the following
INSERT statement is rolled back and returns an error because it attempts to insert a row for department number 30, which cannot be selected using the
The view could optionally have been constructed specifying the
WITH READ ONLY clause, which prevents any updates, inserts, or deletes from being done to the base table through the view. If no
WITH clause is specified, the view, with some restrictions, is inherently updatable.
Oracle9i SQL Reference for detailed syntax, restriction, and authorization information relating to creating and maintaining views
You can also create views that specify more than one base table or view in the
FROM clause. These are called join views. The following statement creates the
division1_staff view that joins data from the
CREATE VIEW division1_staff AS SELECT ename, empno, job, dname FROM emp, dept WHERE emp.deptno IN (10, 30) AND emp.deptno = dept.deptno;
An updatable join view is a join view where
DELETE operations are allowed. See "Updating a Join View" for further discussion.
When a view is created, Oracle expands any wildcard (*) in a top-level view query into a column list. The resulting query is stored in the data dictionary; any subqueries are left intact. The column names in an expanded column list are enclosed in quote marks to account for the possibility that the columns of the base object were originally entered with quotes and require them for the query to be syntactically correct.
As an example, assume that the
dept view is created as follows:
Oracle stores the defining query of the
dept view as:
Views created with errors do not have wildcards expanded. However, if the view is eventually compiled without errors, wildcards in the defining query are expanded.
If there are no syntax errors in a
CREATE VIEW statement, Oracle can create the view even if the defining query of the view cannot be executed. In this case, the view is considered "created with errors." For example, when a view is created that refers to a nonexistent table or an invalid column of an existing table, or when the view owner does not have the required privileges, the view can be created anyway and entered into the data dictionary. However, the view is not yet usable.
To create a view with errors, you must include the
FORCE option of the
CREATE VIEW statement.
By default, views with errors are not created as
VALID. When you try to create such a view, Oracle returns a message indicating the view was created with errors. The status of a view created with errors is
INVALID. If conditions later change so that the query of an invalid view can be executed, the view can be recompiled and be made valid (usable). For information changing conditions and their impact on views, see "Managing Object Dependencies".
An updatable join view (also referred to as a modifiable join view) is a view that contains more than one table in the top-level
FROM clause of the
SELECT statement, and is not restricted by the
WITH READ ONLY clause.
There are some restrictions and conditions which can affect whether a join view is updatable. Specifics are listed in the description of the
Additionally, if a view is a join on other nested views, then the other nested views must be mergeable into the top level view. For a discussion of mergeable and unmergeable views, and more generally, how the optimizer optimizes statements referencing views, see Oracle9i Database Concepts and Oracle9i Database Performance Guide and Reference.
There are data dictionary views that indicate whether the columns in a join view are updatable. See Table 20-1, "UPDATABLE_COLUMNS Views" for descriptions of these views.
The rules for updatable join views are as follows:
All updatable columns of a join view must map to columns of a key-preserved table. See "Key-Preserved Tables" for a discussion of key-preserved tables. If the view is defined with the
Rows from a join view can be deleted as long as there is exactly one key-preserved table in the join. If the view is defined with the
Examples illustrating these rules, and a discussion of key-preserved tables, are presented in succeeding sections.
The examples given work only if you explicitly define the primary and foreign keys in the tables, or define unique indexes. Following are the appropriately constrained table definitions for
CREATE TABLE dept ( deptno NUMBER(4) PRIMARY KEY, dname VARCHAR2(14), loc VARCHAR2(13)); CREATE TABLE emp ( empno NUMBER(4) PRIMARY KEY, ename VARCHAR2(10), job VARCHAR2(9), mgr NUMBER(4), sal NUMBER(7,2), comm NUMBER(7,2), deptno NUMBER(2), FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO));
You could also omit the primary and foreign key constraints listed above, and create a
UNIQUE INDEX on
dept (deptno) to make the following examples work.
The following statement created the
emp_dept join view which is referenced in the examples:
CREATE VIEW emp_dept AS SELECT emp.empno, emp.ename, emp.deptno, emp.sal, dept.dname, dept.loc FROM emp, dept WHERE emp.deptno = dept.deptno AND dept.loc IN ('DALLAS', 'NEW YORK', 'BOSTON');
The concept of a key-preserved table is fundamental to understanding the restrictions on modifying join views. A table is key preserved if every key of the table can also be a key of the result of the join. So, a key-preserved table has its keys preserved through a join.
The key-preserving property of a table does not depend on the actual data in the table. It is, rather, a property of its schema. For example, if in the
emp table there was at most one employee in each department, then
deptno would be unique in the result of a join of
dept would still not be a key-preserved table.
SELECT all rows from
emp_dept, the results are:
EMPNO ENAME DEPTNO DNAME LOC ---------- ---------- ------- -------------- ----------- 7782 CLARK 10 ACCOUNTING NEW YORK 7839 KING 10 ACCOUNTING NEW YORK 7934 MILLER 10 ACCOUNTING NEW YORK 7369 SMITH 20 RESEARCH DALLAS 7876 ADAMS 20 RESEARCH DALLAS 7902 FORD 20 RESEARCH DALLAS 7788 SCOTT 20 RESEARCH DALLAS 7566 JONES 20 RESEARCH DALLAS 8 rows selected.
In this view,
emp is a key-preserved table, because
empno is a key of the
emp table, and also a key of the result of the join.
dept is not a key-preserved table, because although
deptno is a key of the
dept table, it is not a key of the join.
The general rule is that any
INSERT statement on a join view can modify only one underlying base table. The following examples illustrate rules specific to
The following example shows an
UPDATE statement that successfully modifies the
UPDATE statement would be disallowed on the
This statement fails with an error (
cannot modify a column which maps to a non key-preserved table), because it attempts to modify the base
dept table, and the
dept table is not key preserved in the
In general, all updatable columns of a join view must map to columns of a key-preserved table. If the view is defined using the
WITH CHECK OPTION clause, then all join columns and all columns taken from tables that are referenced more than once in the view are not modifiable.
So, for example, if the
emp_dept view were defined using
WITH CHECK OPTION, the following
UPDATE statement would fail:
The statement fails because it is trying to update a join column.
You can delete from a join view provided there is one and only one key-preserved table in the join.
DELETE statement works on the
DELETE statement on the
emp_dept view is legal because it can be translated to a
DELETE operation on the base
emp table, and because the
emp table is the only key-preserved table in the join.
If you were to create the following view, a
DELETE operation could not be performed on the view because both
e2 are key-preserved tables:
CREATE VIEW emp_emp AS SELECT e1.ename, e2.empno, deptno FROM emp e1, emp e2 WHERE e1.empno = e2.empno;
If a view is defined using the
WITH CHECK OPTION clause and the key-preserved table is repeated, then rows cannot be deleted from such a view.
CREATE VIEW emp_mgr AS SELECT e1.ename, e2.ename mname FROM emp e1, emp e2 WHERE e1.mgr = e2.empno WITH CHECK OPTION;
No deletion can be performed on this view because the view involves a self-join of the table that is key preserved.
INSERT statement on the
emp_dept view succeeds:
This statement works because only one key-preserved base table is being modified (
emp), and 40 is a valid
deptno in the
dept table (thus satisfying the
FOREIGN KEY integrity constraint on the
INSERT statement, such as the following, would fail for the same reason that such an
UPDATE on the base
emp table would fail: the
FOREIGN KEY integrity constraint on the
emp table is violated (because there is no
INSERT statement would fail with an error (
cannot modify more than one base table through a view):
INSERT cannot implicitly or explicitly refer to columns of a non-key-preserved table. If the join view is defined using the
WITH CHECK OPTION clause, then you cannot perform an
INSERT to it.
The views described in Table 20-1 can assist you when modifying join views.
Shows all columns in all tables and views that are modifiable.
Shows all columns in all tables and views accessible to the user that are modifiable.
Shows all columns in all tables and views in the user's schema that are modifiable.
The updatable columns in view
emp_dept are shown below.
SELECT COLUMN_NAME, UPDATABLE FROM USER_UPDATABLE_COLUMNS WHERE TABLE_NAME = 'EMP_DEPT'; COLUMN_NAME UPD ------------------------------ --- EMPNO YES ENAME YES DEPTNO YES SAL YES DNAME NO LOC NO 6 rows selected.
You use the
ALTER VIEW statement only to explicitly recompile a view that is invalid. If you want to change the definition of a view, see "Replacing Views".
ALTER VIEW statement allows you to locate recompilation errors before run time. To ensure that the alteration does not affect the view or other objects that depend on it, you can explicitly recompile a view after altering one of its base tables.
To use the
ALTER VIEW statement, the view must be in your schema, or you must have the
ALTER ANY TABLE system privilege.
You can drop any view contained in your schema. To drop a view in another user's schema, you must have the
DROP ANY VIEW system privilege. Drop a view using the
DROP VIEW statement. For example, the following statement drops the
To replace a view, you must have all the privileges required to drop and create a view. If the definition of a view must change, the view must be replaced; you cannot change the definition of a view. You can replace views in the following ways:
CREATE VIEWstatement that contains the
OR REPLACEoption. The
OR REPLACEoption replaces the current definition of a view and preserves the current security authorizations. For example, assume that you created the
sales_staffview as shown earlier, and, in addition, you granted several object privileges to roles and other users. However, now you need to redefine the
sales_staffview to change the department number specified in the
WHEREclause. You can replace the current version of the
sales_staffview with the following statement:
Before replacing a view, consider the following effects:
CHECK OPTIONwas previously defined but not included in the new view definition, the constraint is dropped.
Sequences are database objects from which multiple users can generate unique integers. You can use sequences to automatically generate primary key values. This section describes various aspects of managing sequences, and contains the following topics:
To create a sequence in your schema, you must have the
CREATE SEQUENCE system privilege. To create a sequence in another user's schema, you must have the
CREATE ANY SEQUENCE privilege.
Create a sequence using the
CREATE SEQUENCE statement. For example, the following statement creates a sequence used to generate employee numbers for the
empno column of the
CACHE option pre-allocates a set of sequence numbers and keeps them in memory so that sequence numbers can be accessed faster. When the last of the sequence numbers in the cache has been used, Oracle reads another set of numbers into the cache.
Oracle might skip sequence numbers if you choose to cache a set of sequence numbers. For example, when an instance abnormally shuts down (for example, when an instance failure occurs or a
SHUTDOWN ABORT statement is issued), sequence numbers that have been cached but not used are lost. Also, sequence numbers that have been used but not saved are lost as well. Oracle might also skip cached sequence numbers after an export and import. See Oracle9i Database Utilities for details.
To alter a sequence, your schema must contain the sequence, or you must have the
ALTER ANY SEQUENCE system privilege. You can alter a sequence to change any of the parameters that define how it generates sequence numbers except the sequence's starting number. To change the starting point of a sequence, drop the sequence and then re-create it. When you perform DDL on sequence numbers you lose the cache values.
Alter a sequence using the
ALTER SEQUENCE statement. For example, the following statement alters the
You can drop any sequence in your schema. To drop a sequence in another schema, you must have the
DROP ANY SEQUENCE system privilege. If a sequence is no longer required, you can drop the sequence using the
DROP SEQUENCE statement. For example, the following statement drops the
When a sequence is dropped, its definition is removed from the data dictionary. Any synonyms for the sequence remain, but return an error when referenced.
A synonym is an alias for a schema object. Synonyms can provide a level of security by masking the name and owner of an object and by providing location transparency for remote objects of a distributed database. Also, they are convenient to use and reduce the complexity of SQL statements for database users.
Synonyms allow underlying objects to be renamed or moved, where only the synonym needs to be redefined and applications based on the synonym continue to function without modification.
You can create both public and private synonyms. A public synonym is owned by the special user group named
PUBLIC and is accessible to every user in a database. A private synonym is contained in the schema of a specific user and available only to the user and the user's grantees.
This section contains the following synonym management information:
To create a private synonym in your own schema, you must have the
CREATE SYNONYM privilege. To create a private synonym in another user's schema, you must have the
CREATE ANY SYNONYM privilege. To create a public synonym, you must have the
CREATE PUBLIC SYNONYM system privilege.
Create a synonym using the
CREATE SYNONYM statement. The underlying schema object need not exist, nor do you need privileges to access the object. The following statement creates a public synonym named
public_emp on the
emp table contained in the schema of
You can drop any private synonym in your own schema. To drop a private synonym in another user's schema, you must have the
DROP ANY SYNONYM system privilege. To drop a public synonym, you must have the
DROP PUBLIC SYNONYM system privilege.
Drop a synonym that is no longer required using
DROP SYNONYM statement. To drop a private synonym, omit the
PUBLIC keyword. To drop a public synonym, include the
For example, the following statement drops the private synonym named
The following statement drops the public synonym named
When you drop a synonym, its definition is removed from the data dictionary. All objects that reference a dropped synonym remain. However, they become invalid (not usable). For more information about how dropping synonyms can affect other schema objects, see "Managing Object Dependencies".
The following views display information about views, synonyms, and sequences:
These views describe synonyms.
These views describe sequences.
These views describe all columns in join views that are updatable.
Oracle9i Database Reference for complete descriptions of these views