ALTER SEQUENCE

ALTER SEQUENCE — change the definition of a sequence generator

Syntax

ALTER SEQUENCE [schema.]sequence [ sequence_option [,... ] ]

where sequence_option can be one of the following:

START WITH value
 QUANTUM SIZE size

Description

ALTER SEQUENCE is used to specify a new starting value of an existing sequence. Future values of the sequence will not be less than the value specified. This operation can not be rolled back and its effects are instantly visible to all other transactions.

The ALTER SEQUENCE command can also change the QUANTUM SIZE for the sequence. This is the size of the block of integer values that will be reserved for a Transaction Engine (TE) to be later assigned.

For details about sequence behavior, see CREATE SEQUENCE.

By default, only the user that creates a sequence database object can ALTER the sequence. This user can GRANT the ALTER privilege on the sequence to other users (see GRANT). The SELECT privilege needs to be granted to other users to SELECT from the sequence.

Parameters

sequence

Name of an existing SEQUENCE to alter.

START WITH value

The START WITH value is optional. It sets the new starting value for the SEQUENCE. The value must be an integer value greater than the current ceiling value for the sequence. The starting value must be an integer value greater than zero (0). The starting value can also be an expression that returns an integer value. Some examples of an expression include, but are not limited to:

A scalar SQL SELECT statement (see SELECT). This must be enclosed by parentheses. This includes all valid SQL SELECT statements, including statements using aggregate functions, GROUP BY, ORDER BY, LIMIT, etc. The SQL SELECT statement must return a number value.
A scalar user defined function (UDF).
A mathematical formula.

QUANTUM SIZE[.var]size

New size indicating the block size for the number of integer values that will be reserved for a given TE. The default value is 100.

Any attempt to modify the start value of a sequence to less than the ceiling value is ignored.

Example

The following example creates a sequence and creates a table that uses the new sequence to generate ID column values.

USE TEST
CREATE SEQUENCE seq_test START WITH 1;
CREATE TABLE tst_table (id INTEGER GENERATED ALWAYS AS IDENTITY (seq_test), column2 STRING);
INSERT INTO tst_table (column2) VALUES ('row1'),('row2');
SELECT * FROM tst_table;
 ID  COLUMN2
 --- --------
  1    row1
  2    row2

SELECT NEXT VALUE FOR seq_test FROM DUAL;
 [NEXT VALUE FOR TEST.SEQ_TEST]
 -------------------------------
                3

INSERT INTO tst_table (column2) VALUES ('row3'),('row4');
SELECT * FROM tst_table;
 ID  COLUMN2
 --- --------
  1    row1
  2    row2
  4    row3
  5    row4

The ALTER SEQUENCE command can use the START WITH option to change the new starting value of a sequence, but can only increase it. In the following example, the START WITH option value of one (1) is less than the current value 6. Therefore, the sequence is not altered.

ALTER SEQUENCE seq_test START WITH 1000;
SELECT NEXT VALUE FOR seq_test FROM DUAL;
 [NEXT VALUE FOR TEST.SEQ_TEST]
 -------------------------------
              1000

The ALTER SEQUENCE command is not transactional. Its effects become visible immediately to all transactions and can’t be rolled back.