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 27.

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

The generation of a sequence number is not affected by the subsequent rollback of the transaction; once generated, that sequence number will not be available again, so gaps can occur. Sequence numbers are accessed by using the pseudo-columns CURRVAL and NEXTVAL.

Example

The following example modifies scott’s sequence ord_seq so that the next sequence number generated will be 10001, and order is guaranteed:

ALTER SEQUENCE scott.ord_seq
     MINVALUE 10001
     ORDER
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset