Oracle Database Administration: The Essential Refe

Name

ALTER SEQUENCE

Synopsis

ALTER SEQUENCE
[schema.]sequence_name
     [INCREMENT BY integer]
     [MAXVALUE integer | NOMAXVALUE]
     [MINVALUE integer | NOMINVALUE]
     [CYCLE | NOCYCLE]
     [CACHE integer | NOCACHE]
     [ORDER | NOORDER]

Changes the characteristics of an Oracle sequence (sequence_name), including starting sequence number, range, number of sequence numbers cached in memory, and whether sequential order is preserved.

Keywords

INCREMENT BY

Specifies the increment between sequence numbers; can be positive or negative (but not 0). The default is 1.

MAXVALUE

Specifies the largest value the sequence number can reach. The default is NOMAXVALUE, which means the maximum value is 10 ²⁷.

MINVALUE

Specifies the smallest value the sequence number can reach. The default is NOMINVALUE, which means the minimum value is 1.

CYCLE

Specifies that when sequence numbers reach MAXVALUE they will begin again at MINVALUE.

NOCYCLE

Specifies that after reaching the maximum value no additional sequence numbers will be generated. This is the default.

CACHE

Specifies how many sequence numbers Oracle will pregenerate and keep in memory. Note that when the database is shut down, unused sequence numbers stored in the cache will be lost. The default is 20.

NOCACHE

Specifies that no sequence numbers are pregenerated to memory.

ORDER

Specifies that sequence numbers are guaranteed to be issued in order of request.

NOORDER

Specifies that sequence numbers are not guaranteed to be generated in the order of request. This is the default.

Notes ...