ALTER SEQUENCE v16
Name
ALTER SEQUENCE — Change the definition of a sequence generator.
Synopsis
ALTER SEQUENCE <name> [ INCREMENT BY <increment> ] [ MINVALUE <minvalue> ] [ MAXVALUE <maxvalue> ] [ CACHE <cache> | NOCACHE ] [ CYCLE ]
Description
ALTER SEQUENCE changes the parameters of an existing sequence generator. Any parameter not specifically set in the ALTER SEQUENCE command retains its prior setting.
Parameters
name
The name (optionally schema-qualified) of a sequence to alter.
increment
The clause INCREMENT BY increment is optional. A positive value makes an ascending sequence, a negative one a descending sequence. If unspecified, the old increment value is maintained.
minvalue
The optional clause MINVALUE minvalue determines the minimum value a sequence can generate. If not specified, the current minimum value is maintained. You can use the keywords NO MINVALUE to set this behavior back to the defaults of 1 and -263-1 for ascending and descending sequences, respectively. However, this term isn't compatible with Oracle databases.
maxvalue
The optional clause MAXVALUE maxvalue determines the maximum value for the sequence. If not specified, the current maximum value is maintained. You can use the keywords NO MAXVALUE to set this behavior back to the defaults of 263-1 and -1 for ascending and descending sequences, respectively. However, this term isn't compatible with Oracle databases.
cache
The optional clause CACHE cache specifies how many sequence numbers to preallocate and store in memory for faster access. The minimum value is 1. Only one value can be generated at a time, i.e., NOCACHE. If unspecified, the old cache value is maintained.
CYCLE
The CYCLE option allows the sequence to wrap around when the maxvalue or minvalue is reached by an ascending or descending sequence. If the limit is reached, the next number generated is the minvalue or maxvalue. If not specified, the old cycle behavior is maintained. You can use the keywords NO CYCLE to alter the sequence so that it doesn't recycle. However, this term isn't compatible with Oracle databases.
Notes
To avoid blocking concurrent transactions that obtain numbers from the same sequence, ALTER SEQUENCE is never rolled back. The changes take effect immediately and aren't reversible.
ALTER SEQUENCE doesn't immediately affect NEXTVAL results in backends, other than the current one, that have preallocated (cached) sequence values. They use up all cached values prior to noticing the changed sequence parameters. The current backend is affected immediately.
Examples
Change the increment and cache values of the sequence serial:
ALTER SEQUENCE serial INCREMENT BY 2 CACHE 5;