Sequence Modification

Modify the attributes of an existing sequence with the ALTER SEQUENCE statement.

The attributes of the sequence that can be modified include:
  • Changing the increment between future values
  • Establishing new minimum or maximum values
  • Changing the number of cached sequence numbers
  • Changing whether the sequence cycles or not
  • Changing whether sequence numbers must be generated in order of request
  • Restarting the sequence
There are two tasks that are not found as part of the creation of the sequence. They are:
  • RESTART: Resets the sequence to the value specified implicitly or explicitly as the starting value when the sequence was created.
  • RESTART WITH numeric-constant: Resets the sequence to the exact numeric constant value. The numeric constant can be any positive or negative value with no non-zero digits to the right of any decimal point.


Restrictions

The data type of a sequence cannot be changed. Instead, you must drop the current sequence and then create a sequence specifying the new data type.

After restarting a sequence or changing to CYCLE, it is possible to generate duplicate sequence numbers. Only future sequence numbers are affected by the ALTER SEQUENCE statement.

All cached sequence values not used by the database manager are lost when a sequence is altered.