Defines a new sequence generator.

Syntax

CREATE SEQUENCE name [ INCREMENT BY increment ]
  [ { NOMINVALUE | MINVALUE minvalue } ]
  [ { NOMAXVALUE | MAXVALUE maxvalue } ]
  [ START WITH start ] [ CACHE cache | NOCACHE ] [ CYCLE ]

Description

You can use the CREATE SEQUENCE command to create a sequence generator. A single-row table named name is generated and initialized. The generator is owned by the user who runs the command.

If you specify a schema, a sequence is created in the specified schema. Otherwise, a sequence is created in the current schema. The sequence name must be different from the name of any other sequence, table, index, or view in the same schema.

After a sequence is created, you can use the NEXTVAL and CURRVAL functions to manage the sequence.

Parameters

Parameter Description
name The name of the sequence to be created. The name can be schema-qualified.
increment The INCREMENT BY increment clause is optional and specifies the value to be added to the current sequence value. A positive value indicates an ascending sequence, and a negative value indicates a descending sequence. Default value: 1.
NOMINVALUE | MINVALUE minvalue The MINVALUE minvalue clause is optional and specifies the minimum value that a sequence can generate. If you do not specify this clause, the default value is used. Default value for ascending sequences: 1. Default value for descending sequences: -263 - 1. Note that you can use the NOMINVALUE keyword to specify the default value.
NOMAXVALUE | MAXVALUE maxvalue The MAXVALUE maxvalue clause is optional and specifies the maximum value that a sequence can generate. If you do not specify this clause, the default value is used. Default value for ascending sequences: 263 - 1. Default value for descending sequences: -1. Note that you can use the NOMAXVALUE keyword to specify the default value.
start The START WITH start clause is optional and specifies the number from which a sequence starts. By default, ascending sequences start from the value that is specified for the minvalue parameter, and descending sequences start from the value that is specified for the maxvalue parameter.
cache The CACHE cache clause is optional and specifies the number of sequence numbers to be allocated and stored in memory for fast access. The minimum value is 1, indicating that only one value can be generated at a time, such as NOCACHE. Default value: 1.
CYCLE Allows a sequence to wrap around when the ascending sequence reaches the maximum value or descending sequence reaches the minimum value. If the limit is reached, the next number generated is the value that is specified by the minvalue or maxvalue parameter.

This parameter is not specified by default. If you do not specify this parameter, any call to the NEXTVAL function after the sequence has reached the maximum value returns an error. Note: You can use the NO CYCLE keyword to specify the default value. This keyword is not compatible with Oracle databases.

Notes

Sequences are based on big integer arithmetic. The sequence range cannot exceed the range of an eight-byte integer. Valid values: -9223372036854775808 to +9223372036854775807. On early platforms, compilers may not support eight-byte integers. In this case, sequences use regular integer arithmetic that ranges from -2147483648 to +2147483647.

If multiple sessions concurrently use a sequence object whose cache parameter is set to a value greater than 1, unexpected results may be retrieved. Each session allocates and caches consecutive sequence values during each access to the sequence object, and increases the final value of the sequence object. Then, the next cache-1 uses of the NEXTVAL function within the session return the preallocated values without touching the sequence object. Therefore, when the session ends, all values that have been allocated but not used within the session are lost and several gaps are generated in the sequence.

Although different sequence values can be assigned to multiple sessions, these values are generated out of order when all sessions are considered. For example, if the cache parameter is set to 10, Session A may retain values from 1 to 10 and return NEXTVAL=1. Then, Session B may retain values from 11 to 20 and return NEXTVAL=11 before Session A generates NEXTVAL=2. Therefore, if the cache parameter is set to 1, NEXTVAL values are generated sequentially. If the cache parameter is set to a value greater than 1, NEXTVAL values are different and may not be generated sequentially. The last value reflects the latest value retained by any session no matter whether the value has been returned by NEXTVAL.

Examples

Create an ascending sequence named serial, that starts from 101:

CREATE SEQUENCE serial START WITH 101;

Select the next number from this sequence:

SELECT serial.NEXTVAL FROM DUAL;

 nextval
---------
     101
(1 row)

Create a sequence named supplier_seq and specify the NOCACHE option.

CREATE SEQUENCE supplier_seq
    MINVALUE 1
    START WITH 1
    INCREMENT BY 1
    NOCACHE;

Select the next number from this sequence:

SELECT supplier_seq.NEXTVAL FROM DUAL;

 nextval
---------
       1
(1 row)