This topic describes how to modify a sequence by using the ALTER SEQUENCE statement.
Syntax
ALTER SEQUENCE [schema. ] sequence_name
[MINVALUE int_value | NOMINVALUE]
[MAXVALUE int_value | NOMAXVALUE]
[INCREMENT BY int_value]
[CACHE int_value | NOCACHE]
[ORDER | NOORDER]
[CYCLE | NOCYCLE];
Parameters
| Parameter | Description |
|---|---|
| MINVALUE int_value | NOMINVALUE | The minimum value of the auto-increment column. Value range: [-1027, (1027-1)]. If NOMINVALUE is specified, the minimum value is 1 when the sequence ascends, and is -(1027-1) when the sequence descends. If no minimum value is specified, NOMINVALUE takes effect by default. |
| MAXVALUE int_value | NOMAXVALUE | The maximum value of the auto-increment column. Value range: [(-1027+1),(1028-1)]. If NOMAXVALUE is specified, the maximum value is (1028-1) when the sequence ascends, and is -1 when the sequence descends. If no maximum value is specified, NOMAXVALUE takes effect by default. |
| INCREMENT BY int_value | The increment of the sequence. The value cannot be 0. If the value is positive, the sequence ascends. If the value is negative, the sequence descends. If this parameter is not specified, the default value is 1. |
| CACHE int_value | NOCACHE | The number of sequence values that the database preallocates and keeps in memory. The default value is 20. |
| ORDER | NOORDER | Specifies whether sequence values are generated in order. The default value is NOORDER. |
| CYCLE | NOCYCLE | Specifies whether sequence values are generated cyclically. The default value is NOCYCLE. |
Examples
View the maximum value of the
seq1sequence.obclient> SELECT sequence_name,max_value FROM USER_SEQUENCES; +---------------+-----------+ | SEQUENCE_NAME | MAX_VALUE | +---------------+-----------+ | SEQ1 | 5 | +---------------+-----------+ 1 row in setModify the maximum value of an auto-increment column to
1024.obclient> ALTER SEQUENCE seq1 MAXVALUE 1024 CYCLE; Query OK, 0 rows affectedView the maximum value of the
seq1sequence again.obclient> SELECT sequence_name,max_value FROM USER_SEQUENCES; +---------------+-----------+ | SEQUENCE_NAME | MAX_VALUE | +---------------+-----------+ | SEQ1 | 1024 | +---------------+-----------+ 1 row in set
Usage notes
You cannot use
ALTER SEQUENCEto modify the value ofSTART WITH. If you want to modify the start value of a sequence, you can modify the value of theINCREMENT BYparameter.For more information about the values of auto-increment columns, see Create a sequence.
