| Oracle® Database Administrator's Guide 10g Release 1 (10. 1) Part Number B10739-01 |
|
|
View PDF |
| Oracle® Database Administrator's Guide 10g Release 1 (10. 1) Part Number B10739-01 |
|
|
View PDF |
This chapter describes the managemen t of views, sequences, and synonyms and contains the following topics:
This section describes aspects of managing views, and contains the following topics:
A
Views can provide a dif ferent representation (such as subsets or supersets) of the data that resides within other tables and views. Views are very powerful because they allow you to tailor the presentation of data to different types of users.
To create a view, you must meet the following requirements:
To create a view in your schema, you must have the CREATE VIEW privilege. To create a view in another user's schema, yo
u must have the CREATE ANY VIEW system privilege. You can acquire these privileges explicitly or through a role.
The owner of the view (whether it is you or another user) must have been explicitly granted privileges to acces
s all objects referenced in the view definition. The owner cannot have obtained these privileges through roles. Also, the fu
nctionality of the view is dependent on the privileges of the view owner. For example, if the owner of the view has only the IN
SERT privilege for Scott's emp table, the view can only be used to insert new rows into the emp tabl
e, not to SELECT, UPDATE, or DELETE rows.
If the owner of the vie
w intends to grant access to the view to other users, the owner must have received the object privileges to the base objects with the
GRANT OPTION or the system privileges with the ADMIN OPTION.
You can create views using the C
REATE 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 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 (named sales_staff_cnst) that INSERT and 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 returns an error because it attempts to insert a row for department
number 30, which cannot be selected using the sales_staff view:
INSERT INTO sales_staff V ALUES (7591, 'WILLIAMS', 30);
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 c
lause is specified, the view, with some restrictions, is inherently updatable.
|
See Also: Oracle Database SQL Reference for syntax and semantics of theCREAT
E VIEW statement |
You can also create views that specify more than one base table or view in the FROM clause. Th
ese are called join views. The following statement creates the division1_staff view that joins data fro
m 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;
An UPDATE, INSERT, and DELETE operations ar
e allowed. See "Updating a Join View" for further discussion.
When a view is created, Oracle Database expands any wildcard (*) in a top-level view quer y into a column list. The resulting query is stored in the data dictionary; any subqueries are left intact. The column names in an ex panded column list are enclosed in quote marks to account for the possibility that the columns of the base object were originally ent ered 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;
The database stor
es 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, w ildcards in the defining query are expanded.
If there are no syntax errors in
a CREATE VIEW statement, the database 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 a
n invalid column of an existing table, or when the view owner does not have the required privileges, the view can be created anyway a
nd entered into the data dictionary. However, the view is not yet usable.
To create a view with errors, you must include the <
code>FORCE clause of the CREATE VIEW statement.
CREATE FORCE VIEW AS ...;
By default, views with errors are created as INVALID. When you try to create such a view, the database returns a mess
age indicating the view was created with errors. 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".
To replace a view, you must have all the privile
ges required to drop and create a view. If the definition of a view must change, the view must be replaced; you cannot use an A
LTER VIEW statement to change the definition of a view. You can replace views in the following ways:
You can drop and re-create the view.
|
Caut ion: When a view is dropped, all grants of corresponding object privileges are revoked from roles and users. Afte r the view is re-created, privileges must be regranted. |
You can redefine the view with a CREATE VIEW statement tha
t contains the OR REPLACE clause. The OR REPLACE clause replaces the current definition of a view and prese
rves the current security authorizations. For example, assume that you created the sales_staff view as shown earlier, an
d, in addition, you granted several object privileges to roles and other users. However, now you need to redefine the sales_sta
ff view to change the department number specified in the WHERE clause. You can replace the current version of the
sales_staff view with the following statement:
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:
Replacing a view replaces the view defin ition in the data dictionary. All underlying objects referenced by the view are not affected.
If a const
raint in the CHECK OPTION was previously defined but not included in the new view definition, the constraint is dropped.
All views and PL/SQL program units dependent on a replaced vie w become invalid (not usable). See "Managing Object Dependencies" for more information on how the database manages such dependencies.
To issue a query or an INSERT, UPDATE, or DELETE statement against a view, you must have
the SELECT, INSERT, UPDATE, or DELETE object privilege for the view, respectively
, either explicitly or through a role.
Views can be queried in the same manner as tables. For example, to query the Divi
sion1_staff view, enter a valid SELECT statement that references the view:
SELECT *
FROM Division1_staff;
ENAME EMPNO JOB DNAME
------------------------------------------------------
CLARK
7782 MANAGER ACCOUNTING
KING 7839 PRESIDENT ACCOUNTING
MILLER 7934 CLERK
ACCOUNTING
ALLEN 7499 SALESMAN SALES
WARD 7521 SALESMAN SALES
JAMES 7900
CLERK SALES
TURNER 7844 SALESMAN SALES
MARTIN 7654 SALESMAN SALES
BLAKE 7
698 MANAGER SALES
With some restrictions, rows can be inserted into, updated in, or deleted from a base tab
le using a view. The following statement inserts a new row into the emp_tab table using the sales_staff vie
w:
INSERT INTO Sales_staff
VALUES (7954, 'OSTER', 30);
Restrictions on DML operations f or views use the following criteria in the order listed:
If a view is defined by a query that contains SET or DISTINCT operators, a GROUP BY clause, or a group function, then rows cannot be inserted into, updated in, or deleted from the base tables using the view.
If a view is defined with WITH CHECK OPTION, then a row cannot be inserted into, or updated in, the base table (using the view), if the view cannot select the row from the ba
se table.
If a NOT NULL column that does not have a DEFAULT clause is omitted
from the view, then a row cannot be inserted into the base table using the view.
If the view was created by using a
n expression, such as DECODE(deptno, 10, "SALES", ...), then rows cannot be inserted into or updated in the
base table using the view.
The constraint created by WITH CHECK OPTION of th
e sales_staff view only allows rows that have a department number of 10 to be inserted into, or updated in, the em
p_tab table. Alternatively, assume that the sales_staff view is defined by the following statement (that is, excl
uding the deptno column):
CREATE VIEW Sales_staff AS
SELECT Empno, Ename
FROM Emp_
tab
WHERE Deptno = 10
WITH CHECK OPTION CONSTRAINT Sales_staff_cnst;
Considering this view definition, you can up
date the empno or ename fields of existing records, but you cannot insert rows into the emp_tab table through the sales_staff view because the view does not let you alter the deptno field. If you had d
efined a DEFAULT value of 10 on the deptno field, then you could perform inserts.
When a user attempts to reference an invalid view, the database returns an error message to the user:
ORA-04063: view 'view_name' has errors
This error message is returned when a view exists but is unu sable due to errors in its query (whether it had errors when originally created or it was created successfully but became unusable la ter because underlying objects were altered or dropped).
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.
The rules for updatable join views are as follows. Views that meet this criteria are said to be inherently updatable.
| Rule | Description |
|---|---|
| General Rule | Any INSERT, UPDATE, or DELETE operation on a join view can modify only one underlying b
ase table at a time. |
UPDATE Rul
e |
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 WITH CHECK OPTION clause, then all join columns and all columns of repeated tables are not updatable. |
DELETE Rule |
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 WITH CHECK OPTION clause and the key preserved table is repeated, then the rows cannot be deleted f
rom the view. |
INSERT Rule |
An INSERT statement must not explicitly or implicitly refer to the columns of
a non-key-preserved table. If the join view is defined with the WITH CHECK OPTION clause, INSERT
statements are not permitted. |
There are data diction ary views that indicate whether the columns in a join view are inherently updatable. See "Using the UPDATABLE_ CO LUMNS Views" for descriptions of these views.
CREATE VIEW statement in the Oracle Database SQL Reference.
If a view is not inher
ently updatable, it can be made updatable by creating an Additionally, if a view is a join on other nested views, then the other nested views must be mergeable in to the top level view. For a discussion of mergeable and unmergeable views, and more generally, how the optimizer optimizes statement s referencing views, see the Oracle Database Pe rformance Tuning Guide. |
Examples illustratin
g the rules for inherently updatable join views, and a discussion of key-preserved tables, are presented in succeeding sections. The
examples in these sections work only if you explicitly define the primary and foreign keys in the tables, or define unique indexes. T
he following statements create the appropriately constrained table definitions for emp and dept.
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 lis
ted in the preceding example, and create a UNIQUE INDEX on dept (deptno) to make the following examples wor
k.
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 restri ctions 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 depar
tment, then 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, 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 DA
LLAS
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-pre
served 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
UPDATE, DELETE, or INSERT statement on a join view can modify only one underlying base table. The fo
llowing examples illustrate rules specific to UPDATE, DELETE, and INSERT statements.
The following example shows an UPDATE statement that successfully modifies the emp_dept
view:
UPDATE emp_dept
SET sal = sal * 1.10
WHERE deptno = 10;
The following emp_dept view:
UPDATE emp_dept
S
ET loc = 'BOSTON'
WHERE ename = 'SMITH';
This statement fails with an error (ORA-01779 cannot mod
ify 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 emp_dept view.
In general, all updatable columns of a join v
iew must map to columns of a key-preserved table. If the view is defined using the WITH CHECK OPTION clause, then all jo
in columns and all columns taken from tables that are referenced more than once in the view are not modifiable.
So, for exampl
e, if the emp_dept view were defined using WITH CHECK OPTION, the following UPDATE statement w
ould fail:
UPDATE emp_dept
SET deptno = 10
WHERE ename = 'SMITH';
The statement f ails because it is trying to update a join column.
|
See Also: Oracle Database SQL Reference for syntax and additional information about theUPDATE statement |
You can delete from a join view provided there is one and only one key-preserved table in the join.
The following DELETE statement works on the emp_dept view:
DELET
E 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.
If you were to create the following view, a DELETE operation
could not 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 b
e deleted from such a view.
CREATE VIEW emp_mgr AS
SELECT e1.ename, e2.ename mname
FROM emp e
1, 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.
|
See Also: Oracle Database SQL Reference for syntax and additional information about theDELETE statement |
The fo
llowing INSERT statement on the emp_dept view succeeds:
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 FO
REIGN KEY integrity constraint on the emp table).
An 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 deptno 77).
INSERT INTO emp_dept (ename, empno, deptno)
VALUES ('KURODA', 9010, 77);
The following INSERT statement would fail with an error (ORA-01776 cannot modify more than one base table through a view):
INSERT INTO emp_dept (empno, ename, loc) VALUES (9010, 'KURODA', 'BOSTON');
An INSER
T cannot implicitly or explicitly refer to columns of a non-key-preserved table. If the join view is defined using the W
ITH CHECK OPTION clause, then you cannot perform an INSERT to it.
INSERT statement |
Views that involv e outer joins are modifiable in some cases. For example:
CREATE VIEW Emp_dept_oj1 AS
SELECT Empno,
Ename, e.Deptno, Dname, Loc
FROM Emp_tab e, Dept_tab d
WHERE e.Deptno = d.Deptno (+);
The statement:
SELECT * FROM Emp_dept_oj1;
Results in:
EMPNO ENAME DEPTNO DNAME
LOC
------- ---------- ------- -------------- -------------
7369 SMITH 40 OPERATIONS BOSTON
7499 ALLEN 30 SALES CHICAGO
7566 JONES 20 RESEARCH DALLAS
7654 MARTIN 3
0 SALES CHICAGO
7698 BLAKE 30 SALES CHICAGO
7782 CLARK 10 ACCOUNTING
NEW YORK
7788 SCOTT 20 RESEARCH DALLAS
7839 KING 10 ACCOUNTING NEW YORK
7844
TURNER 30 SALES CHICAGO
7876 ADAMS 20 RESEARCH DALLAS
7900 JAMES 30
SALES CHICAGO
7902 FORD 20 RESEARCH DALLAS
7934 MILLER 10 ACCOUNTING NEW YO
RK
7521 WARD 30 SALES CHICAGO
14 rows selected.
Columns in the base emp_tab table of emp_dept_oj1 are modifiable through the view, because emp_tab is a key-preserved table in the jo
in.
The following view also contains an outer join:
CREATE VIEW Emp_dept_oj2 AS SELECT e.Empno, e.Ename, e.Deptno, d.Dname, d.Loc FROM Emp_tab e, Dept_tab d WHERE e.Deptno (+) = d.Deptno;
The statement:
SELECT * FROM Emp_dept_oj2;
Results in:
EMPNO ENAME DEPTNO DNAME LOC
---------- ---------- --------- --
------------ ----
7782 CLARK 10 ACCOUNTING NEW YORK
7839 KING 10 ACCOUNTING NEW YORK
793
4 MILLER 10 ACCOUNTING NEW YORK
7369 SMITH 20 RESEARCH DALLAS
7876 ADAMS 20
RESEARCH DALLAS
7902 FORD 20 RESEARCH DALLAS
7788 SCOTT 20 RESEARCH DALL
AS
7566 JONES 20 RESEARCH DALLAS
7499 ALLEN 30 SALES CHICAGO
7698 BLAKE
30 SALES CHICAGO
7654 MARTIN 30 SALES CHICAGO
7900 JAMES 30 SALES
CHICAGO
7844 TURNER 30 SALES CHICAGO
7521 WARD 30 SALES CHICAGO
OPERATIONS BOSTON
15 rows selected.
In this view, emp_tab is no longer a key-preser
ved table, because the empno column in the result of the join can have nulls (the last row in the preceding SELECT
statement). So, UPDATE, DELETE, and INSERT operations cannot be performed on this view
.
In the case of views containing an outer join on other nested views, a table is key preserved if the view or views containin g the table are merged into their outer views, all the way to the top. A view which is being outer-joined is currently merged only if it is "simple." For example:
SELECT Col1, Col2, ... FROM T;
The select list of the view ha
s no expressions, and there is no WHERE clause.
Consider the following set of views:
CREATE VIEW Emp_v AS
SELECT Empno, Ename, Deptno
FROM Emp_tab;
CREATE VIEW Emp_dept_oj1 AS
SELECT e.*, Loc, d.Dname
FROM Emp_v e, Dept_tab d
WHERE e.Deptno = d.Deptno (+);
In these examples, emp_v is mer
ged into emp_dept_oj1 because emp_v is a simple view, and so emp_tab is a key-preserved table.
But if emp_v is changed as follows:
CREATE VIEW Emp_v_2 AS
SELECT Empno, Ename, Deptn
o
FROM Emp_tab
WHERE Sal > 1000;
Then, because of the presence of the WHERE clause, <
code>emp_v_2 cannot be merged into emp_dept_oj1, and hence emp_tab is no longer a key-preserved tabl
e.
If you are in doubt whether a view is modifiable, then you can SELECT from the view USER_UPDATABLE_COLUM
NS to see if it is. For example:
SELECT * FROM USER_UPDATABLE_COLUMNS WHERE TABLE_NAME = 'EMP_DE PT_VIEW';
This might return:
OWNER TABLE_NAME COLUMN_NAM UPD
---------- --
-------- ---------- ---
SCOTT EMP_DEPT_V EMPNO NO
SCOTT EMP_DEPT_V ENAME NO
SCOTT
EMP_DEPT_V DEPTNO NO
SCOTT EMP_DEPT_V DNAME NO
SCOTT EMP_DEPT_V LOC
NO
5 rows selected.
The views described in the following table can assist you to identify inherently updatable join views.
| View | Description | |||||||||
|---|---|---|---|---|---|---|---|---|---|---|
DBA_UPDATABLE_COLUMNS
|
Shows all columns in all tables and views that are modifiable. | ALL_UPDATABLE_COLUMNS |
Shows all columns in all tables and views accessible to the user that are modifiable. | |||||||
USER_UPDATABLE_COLUMNS |
Shows all columns in all tables and views in the user's schema that are modifiable. |
|
See Also: Oracle Database SQL Reference for syntax and additional information about theALTER VIEW statement |
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 emp_dept view:
DROP VIEW emp_dept;
|
See Also: Or acle Database SQL Reference for syntax and additional information about theDROP VIEW statement |
This section describes aspects of managing sequences, and contains the following topics:
Seque nces are database objects from which multiple users can generate unique integers. The sequence generator generates sequentia l numbers, which can help to generate unique primary keys automatically, and to coordinate keys across multiple rows or tables.
< p>Without sequences, sequential values can only be produced programmatically. A new primary key value can be obtained by selecting th e most recently produced value and incrementing it. This method requires a lock during the transaction and causes multiple users to w ait for the next value of the primary key; this waiting is known as serialization. If developers have such construct s in applications, then you should encourage the developers to replace them with access to sequences. Sequences eliminate serializati on and improve the concurrency of an application.To create a sequence in your schema, you must have the CREATE SEQUENCE system privilege. To create a seq
uence 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 emp table:
CREATE SEQUENC
E emp_sequence
INCREMENT BY 1
START WITH 1
NOMAXVALUE
NOCYCLE
CACHE 10;
Notice that several
parameters can be specified to control the function of sequences. You can use these parameters to indicate whether the sequence is a
scending or descending, the starting point of the sequence, the minimum and maximum values, and the interval between sequence values.
The NOCYCLE option indicates that the sequence cannot generate more values after reaching its maximum or minimum value.
The CACHE clause preallocates 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, the database reads another set of numbers into th
e cache.
The database might skip sequence numbers if you choose to cache a set of sequence numbers. For example, when an insta
nce abnormally shuts down (for example, when an instance failure occurs or a SHUTDOWN ABORT statement is issued), sequen
ce numbers that have been cached but not used are lost. Also, sequence numbers that have been used but not saved are lost as well. Th
e database might also skip cached sequence numbers after an export and import. See Oracle Database Utilities for details.
|
See Also:
|
To alter a sequence, your schema must contain the sequence, or you must have the ALTER ANY SE
QUENCE system privilege. You can alter a sequence to change any of the parameters tha
t define how it generates sequence numbers except the sequence starting number. To change the starting point of a sequence, drop the
sequence and then re-create it.
Alter a sequence using the ALTER SEQUENCE statement. For example, the following statement alters the emp_sequence:
ALTER SEQUENCE
emp_sequence
INCREMENT BY 10
MAXVALUE 10000
CYCLE
CACHE 20;
|
See Also: Oracle Database SQL Reference for syntax and additional information about theALTER
SEQUENCE statement |
To use a sequence, your schema must contain the sequence or you must hav
e been granted the SELECT object privilege for another user's sequence. Once a
sequence is defined, it can be accessed and incremented by multiple users (who have SELECT object privilege for the sche
ma containing the sequence) with no waiting. The database does not wait for a transaction that has incremented a sequence to complete
before that sequence can be incremented again.
The examples outlined in the followi
ng sections show how sequences can be used in master/detail table relationships. Assume an order entry system is partially comprised
of two tables, orders_tab (master table) and line_items_tab (detail table), that hold information about cus
tomer orders. A sequence named order_seq is defined by the following statement:
CREATE SEQ
UENCE Order_seq
START WITH 1
INCREMENT BY 1
NOMAXVALUE
NOCYCLE
CACHE 20;
A se
quence is referenced in SQL statements with the NEXTVAL and CURRVAL pseudocolumns; each new sequence number
is generated by a reference to the sequence pseudocolumn NEXTVAL, while the current sequence number can be repeatedly r
eferenced using the pseudo-column CURRVAL.
NEXTVAL and CURRVAL are not reserved words or keywords and can be used as pseudocolumn names i
n SQL statements such as SELECT, INSERT, or UPDATE.
To generate and use a sequence number, re
ference seq_name.NEXTVAL. For example, assume a customer places an order. The sequence number can be referenced
in a values list. For example:
INSERT INTO Orders_tab (Orderno, Custno)
VALUES (Order_seq.NEXTVAL,
1032);
Or, the sequence number can be referenced in the SET clause of an UPDATE statement. For
example:
UPDATE Orders_tab
SET Orderno = Order_seq.NEXTVAL
WHERE Orderno = 10112;
The sequence number can also be referenced outermost SELECT of a query or subquery. For example:
SELECT Order_seq.NEXTVAL FROM dual;
As defined, the first reference to order_seq.NEXTVAL returns the
value 1. Each subsequent statement that references order_seq.NEXTVAL generates the next sequence number (2, 3, 4,. . .).
The pseudo-column NEXTVAL can be used to generate as many new sequence numbers as necessary. However, only a single seq
uence number can be generated for each row. In other words, if NEXTVAL is referenced more than once in a single statemen
t, then the first reference generates the next number, and all subsequent references in the statement return the same number.
Once a sequence number is generated, the sequence number is available only to the session that generated the number. Independent of t
ransactions committing or rolling back, other users referencing order_seq.NEXTVAL obtain unique values. If two users are
accessing the same sequence concurrently, then the sequence numbers each user receives might have gaps because sequence numbers are
also being generated by the other user.
To use or refer to the current sequence value of your session, reference seq_name.CURRVAL. CURRVAL can only be used if seq_name.NEXTVAL has been referenced in the current user session (i
n the current or a previous transaction). CURRVAL can be referenced as many times as necessary, including multiple times
within the same statement. The next sequence number is not generated until NEXTVAL is referenced. Continuing with the p
revious example, you would finish placing the customer's order by inserting the line items for the order:
INSERT INTO Line_items_tab (Orderno, Partno, Quantity)
VALUES (Order_seq.CURRVAL, 20321, 3);
INSERT INTO Line_items_tab (Orde
rno, Partno, Quantity)
VALUES (Order_seq.CURRVAL, 29374, 1);
Assuming the INSERT statement given in the
previous section generated a new sequence number of 347, both rows inserted by the statements in this section insert rows with order
numbers of 347.
CURRVAL and NEXTVAL can be used in the following places:<
/p>
VALUES clause of INSERT statements
The SELE
CT list of a SELECT statement
The SET clause of an UPDATE
statement
CURRVAL and NEXTVAL cannot be used in these places:
A subquery
A view query or materialized view query
A SELECT s
tatement with the DISTINCT operator
A SELECT statement with a GROUP BY or ORDER BY clause
A SELECT statement that is
combined with another SELECT statement with the UNION, INTERSECT, or MINUS set op
erator
The WHERE clause of a SELECT statement
CREATE TABLE or ALTER TABLE statement
The condition of a CHECK constraint
Sequence numbers can be kept in the sequence cache in the System Global Area (SGA). Sequence numb ers can be accessed more quickly in the sequence cache than they can be read from disk.
The sequence cache consists of entries . Each entry can hold many sequence numbers for a single sequence.
Follow these guidelines for fast access to all sequence num bers:
Be sure the sequence cache can hold all the sequences used concurrently by your applications.
< /li>Increase the number of values for each sequence held in the sequence cache.
When an application accesses a sequence in the sequence cache, the sequence numbers are read quickly. However, if an application accesses a sequence that is not in the cache, then the sequence must be read from disk to the cache befor e the sequence numbers are used.
If your applications use many sequences concurrently, then your sequence cache might not be l arge enough to hold all the sequences. In this case, access to sequence numbers might often require disk reads. For fast access to al l sequences, be sure your cache has enough entries to hold all the sequences used concurrently by your applications.
When a sequence is
read into the sequence cache, sequence values are generated and stored in a cache entry. These values can then be accessed quickly. T
he number of sequence values stored in the cache is determined by the CACHE par
ameter in the CREATE SEQUENCE statement. The default value for thi
s parameter is 20.
This CREATE SEQUENCE statement creates t
he seq2 sequence so that 50 values of the sequence are stored in the SEQUENCE cache:
CREATE SEQUENCE Seq2
CACHE 50;<
/strong>
The first 50 values of seq2 can then be read from the cache. When the 51st value is accessed, the n
ext 50 values will be read from disk.
Choosing a high value for CACHE lets you access more successive sequence nu
mbers with fewer reads from disk to the sequence cache. However, if there is an instance failure, then all sequence values in the cac
he are lost. Cached sequence numbers also could be skipped after an export and import if transactions continue to access the sequence
numbers while the export is running.
If you use the NOCACHE option in t
he CREATE SEQUENCE statement, then the values of the sequence are
not stored in the sequence cache. In this case, every access to the sequence requires a disk read. Such disk reads slow access to the
sequence. This CREATE SEQUENCE statement creates the SEQ3 sequence so that its values are nev
er stored in the cache:
CREATE SEQUENCE Seq3
NOCACHE;
You can drop any sequence i
n 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 state
ment. For example, the following statement drops the order_seq sequence:
DROP SEQUENCE ord er_seq;
When a sequence is dropped, its definition is removed from the data dictionary. Any synonyms for the sequence rem ain, but return an error when referenced.
DROP SEQUENCE statement |
|
See Also: Oracle Database SQL Reference for syntax and additional information about theCREATE SYNONYM statement |
You can successfully use
any private synonym contained in your schema or any public synonym, assuming that you have the necessary privileges to access the und
erlying object, either explicitly, from an enabled role, or from PUBLIC. You can also reference any private synonym cont
ained in another schema if you have been granted the necessary object privileges for the private synonym.
You can only referen
ce another user's synonym using the object privileges that you have been granted. For example, if you have the SELECT pr
ivilege for the jward.emp_tab synonym, then you can query the jward.emp_tab synon
ym, but you cannot insert rows using the synonym for jward.emp_tab.
A synonym can be referenced in a
DML statement the same way that the underlying object of the synonym can be referenced. For example, if a synonym named emp_ta
b refers to a table or view, then the following statement is valid:
INSERT INTO Emp_tab (Empno,
Ename, Job)
VALUES (Emp_sequence.NEXTVAL, 'SMITH', 'CLERK');
If the synonym named fire_emp refers to a s
tandalone procedure or package procedure, then you could execute it with the command
EXECUTE Fire_emp(7 344);
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 syst
em privilege.
Drop a synonym that is no long
er required using DROP SYNONYM statement. To drop a private synonym, omit the PUBLIC keyword. To drop a pub
lic synonym, include the PUBLIC keyword.
For example, the following statement drops the private synonym named
DROP SYNONYM emp;
The following statement drops the public synonym named
DROP PUBLIC SYNONYM public_emp;
When you drop a synonym, its definiti on 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 Obj ect Dependencies".
|
See Also: Oracle Database SQL Reference for syntax and additional information about theDROP SYNONYM statemen
t |
The following views displa y information about views, synonyms, and sequences:
| View | Description |
|---|---|
DBA_VIEWS
|
DBA view describes all views in the database. USER view is restricted to views owned by the
current user. |
DBA_SYNONYMS
<
p>ALL_SYNONYMS
|
These views descr ibe synonyms. |
DBA_SEQUENCES
|
These views de scribe sequences. |
DBA_UPDATABLE_COLU
MNS
|
These views describe all columns in join views that are updatable. |
|
 Copyright © 2001, 2003&n bsp;Oracle Corporation All Rights Reserved. |
|
