Oracle in a Nutshell

Name

CREATE/ALTER/DROP SEQUENCE

Synopsis

CREATE Syntax:

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

ALTER Syntax:

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

DROP Syntax:

DROP SEQUENCE [schema.]sequence_name

Creates, modifies, or removes an Oracle sequence that can be used to automatically generate sequential numbers during database operations. 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 pseudocolumns CURRVAL and NEXTVAL.

The DROP SEQUENCE and CREATE SEQUENCE statements can be issued sequentially to restart a sequence at a lower number. However, all GRANTs to the sequence will also have to be re-created.

Keywords

INCREMENT BY

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

START WITH

Specifies the first sequence number to be generated. The default is the MINVALUE for ascending sequences and MAXVALUE for descending sequences.

MAXVALUE

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