Summary: in this tutorial, you will learn how to use the Oracle
ALTER SEQUENCE statement to change the attributes and behavior of a sequence object.
ALTER SEQUENCE Overview
ALTER SEQUENCE statement allows you to change the increment, minimum value, maximum value, cached numbers, and behavior of a sequence object.
Here is the basic syntax of the
ALTER SEQUENCE statement:
ALTER SEQUENCE schema_name.sequence_name [INCREMENT BY interval] [MAXVALUE max_value | NOMAXVALUE] [MINVALUE min_value | NOMINVALUE] [CYCLE | NOCYCLE] [CACHE cache_size | NOCACHE] [ORDER | NOORDER];
All the parameters have the same meaning as described in the
CREATE SEQUENCE statement.
When you change sequence’s attributes, Oracle performs some validations behind the scenes. For example, Oracle will issue an error if you change the maximum number of a sequence to a value that is less than the current sequence number.
DROP SEQUENCE schema_name.sequence_name; CREATE SEQUENCE schema_name.sequence_name START WITH new_value;
Notice that the
ALTER SEQUENCE statement takes effect on only the future sequence numbers.
ALTER SEQUENCE example
The following statement uses the
CREATE SEQUENCE statement to create a new sequence called
CREATE SEQUENCE invoice_seq START WITH 20190001;
This example uses the
ALTER SEQUENCE statement to turn on the
CACHE for the
ALTER SEQUENCE invoice_seq CACHE 10;
To change the
START WITH number, you first drop the
invoice_seq sequence first:
DROP SEQUENCE invoice_seq;
And then recreate it:
CREATE SEQUENCE invoice_seq START WITH 20200001 CACHE 10;
In this tutorial, you’ve learned how to use the Oracle
ALTER SEQUENCE statement to change the increment, minimum value, maximum value, cached numbers, and behavior of a sequence object.