| Oracle Server Administrator's Guide
This chapter describes aspects of view management, and includes the following topics:
Before attempting tasks described in this chapter, familiarize yourself with the concepts in Chapter 10, "Guidelines for Managing Schema Objects".
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 includes the following topics:
To create a view, you must fulfill the requirements listed below:
You can create views using the SQL command CREATE VIEW. Each view is defined by a query that references tables, snapshots, or other views. The query that defines a view cannot contain the ORDER BY or FOR UPDATE clauses. For example, the following statement creates a view on a subset of data in the EMP table:
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 that INSERT and 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:
INSERT INTO sales_staff VALUES (7584, 'OSTER', 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 could not be selected using the SALES_STAFF view:
INSERT INTO sales_staff VALUES (7591, 'WILLIAMS', 30);
The following statement creates a view that joins data from the EMP and DEPT tables:
CREATE VIEW division1_staff AS SELECT ename, empno, job, dname FROM emp, dept WHERE emp.deptno IN (10, 30) AND emp.deptno = dept.deptno;
The DIVISION1_STAFF view joins information from the EMP and DEPT tables. The CHECK OPTION is not specified in the CREATE VIEW statement for this view.
In accordance with the ANSI/ISO standard, Oracle expands any wildcard in a top-level view query into a column list when a view is created and stores the resulting query 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:
CREATE VIEW dept AS SELECT * FROM scott.dept;
Oracle stores the defining query of the DEPT view as:
SELECT "DEPTNO", "DNAME", "LOC" FROM scott.dept
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; the view is considered "created with errors." For example, when a view is created that refers to a non-existent 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 command:
CREATE FORCE VIEW AS ....;
By default, views are not created with errors. When a view is created with errors, 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 become valid (usable).
See Also: For information changing conditions and their impact on views, see "Managing Object Dependencies" on page 17-24.
A modifiable join view is a view that contains more than one table in the top-level FROM clause of the SELECT statement, and that does not contain any of the following:
With some restrictions, you can modify views that involve joins. If a view is a join on other nested views, then the other nested views must be mergeable into the top level view.
The examples in following sections use the EMP and DEPT tables. These examples work only if you explicitly define the primary and foreign keys in these tables, or define unique indexes. Following are the appropriately constrained table definitions for EMP and DEPT:
CREATE TABLE dept (
deptno NUMBER(4) PRIMARY KEY,
CREATE TABLE emp (
empno NUMBER(4) PRIMARY KEY,
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.
See Also: For more information about mergeable views see Chapter 5 in the Oracle8 Server Tuning manual.
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.
Note: It is not necessary that the key or keys of a table be selected for it to be key preserved. It is sufficient that if the key or keys were selected, then they would also be key(s) of the result of the join.
Attention: 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 and not of the data in the table. For example, if in the EMP table there was at most one employee in each department, then DEPT.DEPTNO would be unique in the result of a join of EMP and DEPT, but DEPT would still not be a key-preserved table.
If you SELECT all rows from EMP_DEPT_VIEW, 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.
Any UPDATE, INSERT, or DELETE statement on a join view can modify only one underlying base table.
SET sal = sal * 1.10
WHERE deptno = 10;
The following UPDATE statement would be disallowed on the EMP_DEPT view:
SET loc = 'BOSTON'
WHERE ename = 'SMITH';
This statement fails with an ORA-01779 error (``cannot modify a column which maps to a non key-preserved table''), because it attempts to modify the underlying DEPT table, and the DEPT table is not key preserved in the EMP_DEPT view.
In general, all modifiable 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 of repeated tables are not modifiable.
So, for example, if the EMP_DEPT view were defined using WITH CHECK OPTION, the following UPDATE statement would fail:
SET deptno = 10
WHERE ename = 'SMITH';
The statement fails because it is trying to update a join column.
The following DELETE statement works on the EMP_DEPT view:
DELETE FROM emp_dept
WHERE ename = 'SMITH';
This 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.
In the following view, a DELETE operation cannot be performed on the view because both E1 and 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 INTO emp_dept (ename, empno, deptno)
VALUES ('KURODA', 9010, 40);
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 EMP table).
An INSERT statement like 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.
INSERT INTO emp_dept (ename, empno, deptno)
VALUES ('KURODA', 9010, 77);
The following INSERT statement would fail with an ORA-01776 error (``cannot modify more than one base table through a view'').
INSERT INTO emp_dept (empno, ename, loc)
VALUES (9010, 'KURODA', 'BOSTON');
An 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 13-1 can assist you when modifying join views.
Shows all columns in all tables and views in the user's schema that are modifiable.
Shows all columns in all tables and views in the DBA schema that are modifiable.
Shows all columns in all tables and views that are modifiable.
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 alter the definition of a view. You can replace views in the following ways:
Warning: When a view is dropped, all grants of corresponding object privileges are revoked from roles and users. After the view is re-created, privileges must be re-granted.
CREATE OR REPLACE VIEW sales_staff AS SELECT empno, ename, deptno FROM emp WHERE deptno = 30 WITH CHECK OPTION CONSTRAINT sales_staff_cnst;
Before replacing a view, consider the following effects:
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 SQL command DROP VIEW. For example, the following statement drops a view named SALES_STAFF:
DROP VIEW sales_staff;
This section describes various aspects of managing sequences, and includes 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 SQL command CREATE SEQUENCE. For example, the following statement creates a sequence used to generate employee numbers for the EMPNO column of the EMP table:
CREATE SEQUENCE emp_sequence INCREMENT BY 1 START WITH 1 NOMAXVALUE NOCYCLE CACHE 10;
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 Oracle8 Server Utilities for details.
See Also: For information about how the Oracle Parallel Server affects cached sequence numbers, see Oracle8 Parallel Server Concepts & Administration.
For performance information on caching sequence numbers, see Oracle8 Server Tuning.
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.
Alter a sequence using the SQL command ALTER SEQUENCE. For example, the following statement alters the EMP_SEQUENCE:
ALTER SEQUENCE emp_sequence INCREMENT BY 10 MAXVALUE 10000 CYCLE CACHE 20;
The initialization parameter SEQUENCE_CACHE_ENTRIES sets the number of sequences that may be cached at any time. If auditing is enabled for your system, allow one additional sequence for the sequence to identify audit session numbers.
If the value for SEQUENCE_CACHE_ENTRIES is too low, Oracle might skip sequence values, as in the following scenario: assume you are using five cached sequences, the cache is full, and SEQUENCE_CACHE_ENTRIES = 4. If four sequences are currently cached, then a fifth sequence replaces the least recently used sequence in the cache and all remaining values (up to the last sequence number cached) in the displaced sequence are lost.
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 SQL command DROP SEQUENCE. For example, the following statement drops the ORDER_SEQ sequence:
DROP SEQUENCE order_seq;
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.
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 includes 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 SQL command CREATE SYNONYM. For example, the following statement creates a public synonym named PUBLIC_EMP on the EMP table contained in the schema of JWARD:
CREATE PUBLIC SYNONYM public_emp FOR jward.emp;
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 the SQL command DROP SYNONYM. To drop a private synonym, omit the PUBLIC keyword; to drop a public synonym, include the PUBLIC keyword.
For example, the following statement drops the private synonym named EMP:
DROP SYNONYM emp;
The following statement drops the public synonym named PUBLIC_EMP:
DROP PUBLIC SYNONYM public_emp;
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).
See Also: For more information about how dropping synonyms can affect other schema objects, see "Managing Object Dependencies" on page 17-24.