Purpose
This statement is used to modify the increment, minimum, and maximum values, cache size, and behavior of an existing sequence. The changes only affect subsequent sequence numbers.
Note
Users can only modify sequences in their own schema or those for which they have the ALTER object privilege.
Syntax
ALTER SEQUENCE [ schema. ] sequence_name
[ INCREMENT BY int_value ]
[ START WITH int_value ]
[ MINVALUE int_value | NOMINVALUE ]
[ MAXVALUE int_value | NOMAXVALUE ]
[ CACHE int_value | NOCACHE ]
[ ORDER | NOORDER ]
[ CYCLE | NOCYCLE ]
[ RESTART ]
Parameters
| Parameter | Description |
|---|---|
| schema. | The schema that contains the sequence. If you omit the schema, the database changes the sequence in the schema in which the current user is located. |
| sequence_name | The name of the sequence to be changed. |
| RESTART | Resets the sequence value. When ascending, the sequence starts from MINVALUE; when descending, the sequence starts from MAXVALUE. |
| START WITH int_value | The starting value of the sequence. The value of int_value ranges from [-(1027-1) ~ (1028-1)]. |
| MINVALUE int_value | The minimum value of the sequence. The value of int_value ranges from [-(1027-1) ~ (1027-1)]. |
| NOMINVALUE | If you specify NOMINVALUE, the minimum value is 1 when ascending, and -(1027-1) when descending. |
| MAXVALUE int_value | The maximum value of the sequence. The value of int_value ranges from [(-1027+1) ~ (1028-1)]. The newly specified MAXVALUE must be greater than the current sequence number. Notice When you specify both MINVALUE and MAXVALUE, MINVALUE must be less than MAXVALUE. The difference between MAXVALUE and MINVALUE must be greater than or equal to the value of int_value in INCREMENT BY. |
| NOMAXVALUE | If you change the value to NOMAXVALUE, the maximum value for ascending sequences is (1028-1), and the maximum value for descending sequences is -1. |
| INCREMENT BY int_value | Changes the increment step for the sequence. int_value cannot be 0. If specified as a positive number, the sequence is ascending; if specified as a negative number, the sequence is descending. The default value is 1 if not specified. |
| CACHE int_value | Changes the number of preallocated increment values in memory. The default value of int_value is 20. The value of CACHE int_value must be greater than 1. If CACHE INT_VALUE is set to 1, it is equivalent to NOCACHE. |
| NOCACHE | Changes the sequence value to not be preallocated. If both CACHE and NOCACHE are omitted, the database defaults to caching 20 sequence numbers. |
| ORDER | Changes the sequence value to be generated in order. |
| NOORDER | If you change the value to NOORDER, it specifies that the sequence values are not guaranteed to be generated in order. |
| CYCLE | Changes the sequence value to be generated in a cycle, meaning the sequence continues to generate values after reaching its maximum or minimum value. For ascending sequences, it generates the minimum value after reaching the maximum value. For descending sequences, it generates the maximum value after reaching the minimum value. Note
|
| NOCYCLE | NOCYCLE is the default value. If you change the value to NOCYCLE, it specifies that the sequence cannot generate more values after reaching its maximum or minimum value. |
Examples
Modify the maximum value of the
seq1sequence and specify that the increment value is recycled.-- First, create the sequence: CREATE SEQUENCE seq1; obclient> ALTER SEQUENCE seq1 MAXVALUE 1024 CYCLE;Change the maximum value and increment value of the
seq3sequence, enable theCYCLEandORDERoptions, and specifyNOCACHEto indicate that the sequence values are not preallocated.-- First, create the sequence: CREATE SEQUENCE seq3; obclient> ALTER SEQUENCE seq3 MAXVALUE 1500 INCREMENT BY 5 CYCLE NOCACHE ORDER;Reset the sequence value.
-- First, create the sequence: CREATE SEQUENCE seq3; obclient> ALTER SEQUENCE seq3 RESTART;Set the starting value and reset the sequence.
-- First, create the sequence: CREATE SEQUENCE seq3; obclient> ALTER SEQUENCE seq3 START WITH 3 RESTART;The
RESTARToption can be placed anywhere in the clause. The following two SQL statements for resetting the sequence value are equivalent.-- First, create the sequence: CREATE SEQUENCE seq3; obclient> ALTER SEQUENCE seq3 START WITH 3 RESTART MINVALUE -100 CYCLE; obclient> ALTER SEQUENCE seq3 RESTART START WITH 3 MINVALUE -100 CYCLE;