ALTER SEQUENCE |V3.2.4|OceanBase Database| docs|Distributed Database

ALTER SEQUENCE

Last Updated：2023-10-24 09:23:03 Updated

Purpose

You can use this statement to modify the increment, minimum value, maximum value, cache count, and behavior of the current sequence. The modification affects only subsequent sequence numbers.

Note

You can operate only sequences in your own schema, or you must have the ALTER object privilege on the target sequence.

Syntax

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

Parameter	Description
schema.	The schema where the sequence is located. If no schema is specified, the database modifies the sequence in the current schema.
sequence_name	The name of the sequence to be modified.
RESTART	Restarts the sequence value. The sequence starts again from `MINVALUE` when the sequence ascends, and from `MAXVALUE` when the sequence descends. Alternatively, the sequence starts from `int_value` by using `START WITH int_value`.
MINVALUE int_value	Modifies the minimum value of the sequence. Value range: [-9223372036854775808, 9223372036854775807]
NOMINVALUE	If `NOMINVALUE` is specified, the minimum value is 1 for an ascending sequence and is -9223372036854775808 for a descending sequence.
MAXVALUE int_value	Modifies the maximum value of the sequence. Value range: [-9223372036854775808, 9223372036854775807] The new `MAXVALUE` cannot be smaller than the current sequence value. Notice When `MINVALUE` and `MAXVALUE` are both specified, `MINVALUE` must be smaller than `MAXVALUE`. The difference between `MAXVALUE` and `MINVALUE` must be greater than or equal to `INCREMENT BY int_value`.
NOMAXVALUE	If `NOMAXVALUE` is specified, the maximum value is 9223372036854775807 when the sequence ascends, and is -1 when the sequence descends.
INCREMENT BY int_value	Modifies the increment of the sequence. `int_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	Modifies the number of auto-increment values pre-allocated in the memory. Default value: 20. The value of `CACHE int_value` must be greater than 1. If the value of `CACHE int_value` is 1, it is equivalent to `NOCACHE`.
NOCACHE	Specifies that the sequence values are not pre-allocated. If both `CACHE` and `NOCACHE` are omitted, the database caches 20 sequence numbers by default.
ORDER	Specifies that the sequence values are generated in order.
NOORDER	If `NOORDER` is specified, it is not ensured that sequence values are generated in order.
CYCLE	Specifies that the sequence values are generated cyclically. That is, when the sequence reaches its maximum or minimum value, it continues to generate values. When the sequence ascends and reaches its maximum value, it generates the minimum value. When the sequence descends and reaches its minimum value, it generates the maximum value. Notice If `CYCLE` is specified and the value of `INCREMENT BY int_value` is smaller than 0, `MINVALUE` must be specified. If `CYCLE` is specified, the value of `CACHE` cannot be greater than the value of `CYCLE`.
NOCYCLE	`NOCYCLE` is the default value. If `NOCYCLE` is specified, the sequence cannot generate more values after it reaches its maximum or minimum value.

Examples

Modify the maximum value of the seq1 sequence and enable cyclic generation of sequence values.
```
obclient> ALTER SEQUENCE seq1 MAXVALUE 1024 CYCLE;
Query OK, 0 rows affected
```
Modify the maximum value and increment of the seq3 sequence, enable CYCLE and ORDER, and specify NOCACHE to indicate that the sequence values are not pre-allocated.
```
obclient> ALTER SEQUENCE seq3 MAXVALUE 1500 INCREMENT BY 5 CYCLE NOCACHE ORDER;
Query OK, 0 rows affected
```

Restart the sequence from 3.

obclient> ALTER SEQUENCE seq3 RESTART;
Query OK, 0 rows affected

obclient> ALTER SEQUENCE seq3 RESTART START WITH 3;
Query OK, 0 rows affected

The sequence can restart from any position. In the following example, the two SQL statements for restarting a sequence are equivalent.

obclient> ALTER SEQUENCE seq3 START WITH 3 RESTART MINVALUE  -100 CYCLE;
Query OK, 0 rows affected

obclient> ALTER SEQUENCE seq3 RESTAR START WITH 3 T MINVALUE  -100 CYCLE;
Query OK, 0 rows affected