SEQUENCE statement to change the increment, minimum and maximum values, cached numbers, and behavior of an existing sequence. This statement affects only future sequence numbers.
CREATE SEQUENCE for additional information on sequences
The sequence must be in your own schema, or you must have the
ALTER object privilege on the sequence, or you must have the
SEQUENCE system privilege.
If you change the
BYvalue before the first invocation of
NEXTVAL, then some sequence numbers will be skipped. Therefore, if you want to retain the original
WITHvalue, you must drop the sequence and re-create it with the original
WITHvalue and the new
If you alter the sequence by specifying the
NOKEEPclause between runtime and failover of a request, then the original value of
NEXTVALis not retained during replay for Application Continuity for that request.
Oracle Database performs some validations. For example, a new
MAXVALUEcannot be imposed that is less than the current sequence number.
SCALE to enable sequence scalability. When
SCALE is specified, a numeric offset is affixed to the beginning of the sequence which removes all duplicates in generated values.
If you specify
SCALE the generated sequence values are all of length
x is the length of the scalable offset (default value is 6), and
y is the maximum number of digits in the sequence
When you use
SCALE it is highly recommended that you not use
ORDER simultaneously on the sequence.
NOEXTEND is the default setting for the
SCALE clause. With the
NOEXTEND setting, the generated sequence values are at most as wide as the maximum number of digits in the sequence
(maxvalue/minvalue). This setting is useful for integration with existing applications where sequences are used to populate fixed width columns.
Modifying a Sequence: Examples
This statement sets a new maximum value for the
customers_seq sequence, which was created in "Creating a Sequence: Example":
ALTER SEQUENCE customers_seq MAXVALUE 1500;
This statement turns on
CACHE for the
ALTER SEQUENCE customers_seq CYCLE CACHE 5;