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. 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 ceiling value.

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

Example

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   

The 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_table
   ID  COLUMN2  
 ----- -------- 
    1    row1   
    2    row2   
    4    row3   
    5    row4   
 1001    row5   
 1002    row6