Open topic with navigation
ALTER SEQUENCE — change the definition of a sequence generator
ALTER SEQUENCE [schema.]sequence [ sequence_option [,... ] ]
sequence_option can be one of the following:
ALTER SEQUENCE is used to specify a new starting value of an existing sequence. The
START WITH option must specify a
value that is greater than the sequence's
ceiling value. A sequence
ceiling value is incremented in blocks of one thousand (1000). See the Example below for an illustration of
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
ALTER privilege on the sequence to other users (see
SELECT privilege needs to be granted to other users to
SELECT from the sequence.
Name (optionally schema-qualified) of an existing
SEQUENCE to alter.
value is optional. It sets the new starting value for 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:
SELECT). This must be enclosed by parentheses. This includes all valid SQL
SELECTstatements, including statements using aggregate functions,
LIMIT, etc. The SQL
SELECTstatement must return a number value.
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.
The following example creates a sequence and creates a table that uses the new sequence to generate ID column values.
USE SCHEMA1 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;TST_SEQ -------- 3
INSERT INTO tst_table (column2) VALUES ('row3'),('row4'); SELECT * FROM tst_table;ID COLUMN2 --- -------- 1 row1 2 row2 4 row3 5 row4
ALTER SEQUENCE command can use the
START WITH option to change the new starting value of a sequence, but only if the value provided is greater than the current
ceiling value. In the following example, the
START WITH option value of one (1) is less than the current
ceiling value. Therefore, the sequence is not altered.
ALTER SEQUENCE seq_test START WITH 1; SELECT NEXT VALUE FOR seq_test FROM DUAL;SEQ_TEST --------- 6
In the following example, the sequence
START WITH option changes the starting value to a value that is greater than the current
ceiling value for the sequence (1000). The
ceiling value for a sequence is always incremented by one thousand (1000). Therefore, if the current
floor value for the sequence is less than 1000, the
START WITH option must assign an integer value that is greater than 1000. This
ALTER command changes the
ceiling value for the sequence to 2000.
ALTER SEQUENCE seq_test START WITH 1001; INSERT INTO tst_table (column2) VALUES ('row5'),('row6'); SELECT * FROM tst_tableID COLUMN2 ----- -------- 1 row1 2 row2 4 row3 5 row4 1001 row5 1002 row6