Oracle7 Server Administrator's Guide

Managing Sequences

This section describes various aspects of managing sequences, and includes the following topics:

Creating Sequences

Altering Sequences

Initialization Parameters Affecting Sequences

Dropping Sequences

Creating Sequences

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 the Oracle7 Server Utilities guide for details.

For performance information on caching sequence numbers, see the Oracle7 Server Tuning manual.

Altering Sequences

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;

Initialization Parameters Affecting Sequences

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.

Dropping Sequences

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.