Modify a sequence

You can use the ALTER SEQUENCE statement to modify a sequence. This topic describes how to modify a sequence.

Syntax

This statement is used to modify the increment step, minimum value, maximum value, cache size, and action of the current sequence. The modification affects only subsequent sequence values.

Note

You can modify only sequences in your schema or those for which you have the ALTER privilege.

ALTER SEQUENCE [ schema. ] sequence_name
{ RESTART [ START WITH int_value ]
|[ MINVALUE int_value | NOMINVALUE ]
|[ MAXVALUE int_value | NOMAXVALUE ]
|[ INCREMENT BY int_value ]
|[ CACHE int_value | NOCACHE ]
|[ ORDER | NOORDER ]
|[ CYCLE | NOCYCLE]
};

Parameters

Examples

1. View the maximum value of the seq1 sequence.

   obclient> SELECT sequence_name,max_value FROM oceanbase.__all_sequence_object WHERE sequence_name='seq1';
   +---------------+-----------+
   | sequence_name | max_value |
   +---------------+-----------+
   | seq1          |         5 |
   +---------------+-----------+
   1 row in set
   

2. Modify the maximum value of the sequence to 1024.

   obclient> ALTER SEQUENCE seq1 MAXVALUE 1024 CYCLE;
   Query OK, 0 rows affected
   

3. Query the maximum value of the seq1 sequence again.

   obclient> SELECT sequence_name,max_value FROM oceanbase.__all_sequence_object WHERE sequence_name='seq1';
   +---------------+-----------+
   | sequence_name | max_value |
   +---------------+-----------+
   | seq1          |      1024 |
   +---------------+-----------+
   1 row in set