ALTER SEQUENCE

Syntax

Purpose

To change the increment, minimum and maximum values, cached numbers, and behavior of an existing sequence. This statement affects only future sequence numbers. For additional information on sequences, see "CREATE SEQUENCE".

Prerequisites

The sequence must be in your own schema, or you must have the ALTER object privilege on the sequence, or you must have the ALTER ANY SEQUENCE system privilege.

Keywords and Parameters

The keywords and parameters in this statement serve the same purposes described in "CREATE SEQUENCE". In addition:

• To restart the sequence at a different number, you must drop and re-create it (see "DROP SEQUENCE").

• If you change the INCREMENT BY value before the first invocation of NEXTVAL, some sequence numbers will be skipped. Therefore, if you want to retain the original START WITH value, you must drop the sequence and re-create it with the original START WITH value and the new INCREMENT BY value.

• Oracle performs some validations. For example, a new MAXVALUE cannot be imposed that is less than the current sequence number.

Examples

This statement sets a new maximum value for the ESEQ sequence:

ALTER SEQUENCE eseq 
   MAXVALUE 1500;

This statement turns on CYCLE and CACHE for the ESEQ sequence:

ALTER SEQUENCE eseq 
   CYCLE
   CACHE 5;