{"id":2256,"date":"2019-06-21T04:06:55","date_gmt":"2019-06-21T11:06:55","guid":{"rendered":"https:\/\/oracletutorial.com\/?page_id=2256"},"modified":"2025-05-03T15:33:17","modified_gmt":"2025-05-03T22:33:17","slug":"oracle-alter-sequence","status":"publish","type":"page","link":"https:\/\/www.oracletutorial.com\/oracle-sequence\/oracle-alter-sequence\/","title":{"rendered":"Oracle ALTER SEQUENCE Statement"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the Oracle ALTER SEQUENCE<\/code> statement to change the attributes and behavior of a sequence object.<\/p>\n\n\n\n

Oracle ALTER SEQUENCE Statement Overview #<\/a><\/h2>\n\n\n\n

The ALTER SEQUENCE<\/code> statement allows you to change the increment, minimum value, maximum value, cached numbers, and behavior of a sequence object.<\/p>\n\n\n\n

Here is the basic syntax of the ALTER SEQUENCE<\/code> statement:<\/p>\n\n\n

ALTER<\/span> SEQUENCE<\/span> schema_name.sequence_name\n    [INCREMENT<\/span> BY<\/span> interval<\/span>]\n    [MAXVALUE max_value | NOMAXVALUE<\/span>]\n    [MINVALUE<\/span> min_value | NOMINVALUE<\/span>]\n    [CYCLE<\/span> | NOCYCLE<\/span>]\n    [CACHE<\/span> cache_size | NOCACHE]\n    [ORDER<\/span> | NOORDER<\/span>];\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
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.<\/p>\n\n\n\n
To change the sequence at a different number, you have to drop<\/a> and re-create<\/a> it as follows:<\/p>\n\n\n
DROP<\/span> SEQUENCE<\/span> schema_name.sequence_name;\n\nCREATE<\/span> SEQUENCE<\/span> schema_name.sequence_name\n    START<\/span> WITH<\/span> new_value;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Notice that the ALTER SEQUENCE<\/code> statement takes effect on only the future sequence numbers.<\/p>\n\n\n\n
Oracle ALTER SEQUENCE Statement example #<\/a><\/h2>\n\n\n\n
The following statement uses the CREATE SEQUENCE<\/a><\/code> statement to create a new sequence called invoice_seq<\/code>:<\/p>\n\n\n
CREATE<\/span> SEQUENCE<\/span> invoice_seq\n    START<\/span> WITH<\/span> 20190001<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
This example uses the ALTER SEQUENCE<\/code> statement to turn on the CACHE<\/code> for the invoice_seq<\/code> sequence:<\/p>\n\n\n
ALTER<\/span> SEQUENCE<\/span> invoice_seq\nCACHE<\/span> 10<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
DROP<\/span> SEQUENCE<\/span> invoice_seq;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
And then recreate it:<\/p>\n\n\n
CREATE<\/span> SEQUENCE<\/span> invoice_seq\n    START<\/span> WITH<\/span> 20200001<\/span>\n    CACHE<\/span> 10<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this tutorial, you’ve learned how to use the Oracle ALTER SEQUENCE<\/code> statement to change the increment, minimum value, maximum value, cached numbers, and behavior of a sequence object.<\/p>\n\n\n\n
Quiz #<\/a><\/h2>\n\n\n\n
To change the START WITH<\/code> number, you first drop<\/a> the invoice_seq<\/code> sequence first:<\/p>\n\n\n
All the parameters have the same meaning as described in the CREATE SEQUENCE<\/a><\/code> statement.<\/p>\n\n\n\n