Creating Sequences
To create a sequence in your schema, you must have the CREATE SEQUENCE syste
m privilege; to create a sequence in another user's schema, you must have th
e CREATE ANY SEQUENCE privilege. Create a sequence using the SQL command CRE
ATE SEQUENCE. For example, the following statement creates a sequence used t
o 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 m
emory 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 num
bers 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, w
hen an instance failure occurs or a SHUTDOWN ABORT statement is issued), seq
uence numbers that have been cached but not used are lost. Also, sequence nu
mbers 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 Utiliti
es for details.
See Also: For information about how the Oracle Parallel Server affects cache
d sequence numbers, see Oracle8 Parallel Server Concepts and Administration.
To create a sequence in your schema, you must have the CREATE SEQUENCE syste
m privilege; to create a sequence in another user's schema, you must have th
e CREATE ANY SEQUENCE privilege. Create a sequence using the SQL command CRE
ATE SEQUENCE. For example, the following statement creates a sequence used t
o 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 m
emory 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 num
bers 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, w
hen an instance failure occurs or a SHUTDOWN ABORT statement is issued), seq
uence numbers that have been cached but not used are lost. Also, sequence nu
mbers 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 Utiliti
es for details.
See Also: For information about how the Oracle Parallel Server affects cache
d sequence numbers, see Oracle8 Parallel Server Concepts and Administration.