Modifies the definition of a sequence generator.

Syntax

ALTER SEQUENCE name [ INCREMENT BY increment ]
  [ MINVALUE minvalue ] [ MAXVALUE maxvalue ]
  [ CACHE cache | NOCACHE ] [ CYCLE ]

Description

You can use the ALTER SEQUENCE command to modify the parameters of a sequence generator. Any parameter that is not specified in the ALTER SEQUENCE command retains its prior setting.

Note To prevent blocking concurrent transactions that retrieve numbers from the same sequence, rollback does not occur when you run the ALTER SEQUENCE command. The changes take effect immediately and are irreversible.

The ALTER SEQUENCE command does not immediately affect NEXTVAL results in backends (other than the current backend) that have preallocated (cached) sequence values. The system uses cached values before detecting the changed sequence parameters. The current backend is affected immediately.

Parameters

Parameter Description
name The name of the sequence to be modified. The name can be schema-qualified.
increment The INCREMENT BY increment clause is optional. A positive value indicates an ascending sequence, and a negative value indicates a descending sequence. If you do not specify this parameter, the old increment value is retained.
minvalue The MINVALUE minvalue clause is optional and specifies the minimum value that a sequence can generate. If you do not specify this parameter, the current minimum value is retained. Note: The NO MINVALUE keyword can be used to specify the default values 1 and-263-1 for ascending and descending orders, respectively. However, this keyword is not compatible with Oracle databases.
maxvalue The MAXVALUE maxvalue clause is optional and specifies the maximum value for the sequence. If you do not specify this parameter, the current maximum value is retained. Note: The NO MAXVALUE keyword can be used to specify the default values 263-1 and -1 for ascending and descending orders, respectively. However, this keyword is not compatible with Oracle databases.
cache The CACHE cache clause is optional and specifies the number of sequence numbers to be preallocated and stored in memory for fast access. The minimum value is 1, indicating that only one value NOCACHE can be generated at a time. If you do not specify this parameter, the previous cached value is retained.
CYCLE Allows a sequence to wrap around when the ascending sequence reaches the maximum value or descending sequence reaches the minimum value. If the constraint is reached, the next number generated is the value that is specified by the minvalue or maxvalue parameter. If you do not specify this parameter, the previous cycle is retained. Note: The NO CYCLE keyword can be used to specify that the sequence does not recycle. However, this keyword is not compatible with Oracle databases.

Example

Modify the increment and cached value of a sequence named serial:

ALTER SEQUENCE serial INCREMENT BY 2 CACHE 5;