Oracle ALTER SEQUENCE

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.

Oracle ALTER SEQUENCE Overview

The 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];
Code language: SQL (Structured Query Language) (sql)

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.

To change the sequence at a different number, you have to drop and re-create it as follows:

DROP SEQUENCE schema_name.sequence_name;

CREATE SEQUENCE schema_name.sequence_name
    START WITH new_value;
Code language: SQL (Structured Query Language) (sql)

Notice that the ALTER SEQUENCE statement takes effect on only the future sequence numbers.

Oracle ALTER SEQUENCE example

The following statement uses the CREATE SEQUENCE statement to create a new sequence called invoice_seq:

CREATE SEQUENCE invoice_seq
    START WITH 20190001;Code language: SQL (Structured Query Language) (sql)

This example uses the ALTER SEQUENCE statement to turn on the CACHE for the invoice_seq sequence:

ALTER SEQUENCE invoice_seq
CACHE 10;Code language: SQL (Structured Query Language) (sql)

To change the START WITH number, you first drop the invoice_seq sequence first:

DROP SEQUENCE invoice_seq;Code language: SQL (Structured Query Language) (sql)

And then recreate it:

CREATE SEQUENCE invoice_seq
    START WITH 20200001
    CACHE 10;Code language: SQL (Structured Query Language) (sql)

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.