Oracle SEQUENCES

Use the CREATE SEQUENCE statement to create a sequence, which is a database object from which multiple users may generate unique integers. You can use sequences to automatically generate primary key values.

When a sequence number is generated, the sequence is incremented, independent of the transaction committing or rolling back. If two users concurrently increment the same sequence, then the sequence numbers each user acquires may have gaps, because sequence numbers are being generated by the other user. One user can never acquire the sequence number generated by another user. After a sequence value is generated by one user, that user can continue to access that value regardless of whether the sequence is incremented by another user.

Sequence numbers are generated independently of tables, so the same sequence can be used for one or for multiple tables. It is possible that individual sequence numbers will appear to be skipped, because they were generated and used in a transaction that ultimately rolled back. Additionally, a single user may not realize that other users are drawing from the same sequence.

After a sequence is created, you can access its values in SQL statements with the CURRVAL pseudocolumn, which returns the current value of the sequence, or the NEXTVAL pseudocolumn, which increments the sequence and returns the new value.

Prerequisites

To create a sequence in your own schema, you must have the CREATE SEQUENCE system privilege.

To create a sequence in another user's schema, you must have the CREATE ANY SEQUENCE system privilege.

Syntax

CREATE SEQUENCE sequence_name
  MINVALUE value
  MAXVALUE value
  START WITH value
  INCREMENT BY value
  CACHE value;

Sequence

Specify the name of the sequence to be created.

If you specify none of the following clauses, then you create an ascending sequence that starts with 1 and increases by 1 with no upper limit. Specifying only INCREMENT BY -1 creates a descending sequence that starts with -1 and decreases with no lower limit.

• To create a sequence that increments without bound, for ascending sequences, omit the MAXVALUE parameter or specify NOMAXVALUE. For descending sequences, omit the MINVALUE parameter or specify the NOMINVALUE.
• To create a sequence that stops at a predefined limit, for an ascending sequence, specify a value for the MAXVALUE parameter. For a descending sequence, specify a value for the MINVALUE parameter. Also specify NOCYCLE. Any attempt to generate a sequence number once the sequence has reached its limit results in an error.
• To create a sequence that restarts after reaching a predefined limit, specify values for both the MAXVALUE and MINVALUE parameters. Also specify CYCLE. If you do not specify MINVALUE, then it defaults to NOMINVALUE, which is the value 1.

INCREMENT BY:

Tells the system how to increment the sequence. If it is positive, the values are ascending; if it is negative, the values are descending.

START WITH:

Tells the system which integer to start with.

MINVALUE / NOMINVALUE:

MINVALUE integer - Tells the system how low the sequence can go.This value can have 28 or fewer digits. MINVALUE must be equal to or less than START WITH and must be less than MAXVALUE.
NOMINVALUE - Indicates a minimum value of 1 for an ascending sequence or -1026 for for a descending sequence. This is the default.

MAXVALUE / NOMAXVALUE:

MAXVALUE integer - Specifies the maximum value the sequence can generate. This value can have 28 or fewer digits.MAXVALUE must be equal to or greater than START WITH and must be greater than MINVALUE.
NOMAXVALUE - Indicates a maximum value of 1027 for an ascending sequence or -1 for for a descending sequence. This is the default.

CYCLE / NOCYCLE:

CYCLE- Causes the sequences to automatically recycle to minvalue when maxvalue is reached for ascending sequences; for descending sequences, it causes a recycle from minvalue back to maxvalue.
NOCYCLE- Causes the sequence to not automatically recycle its values when minimum or maximum thresholds are met.

CACHE / NOCACHE:

CACHE-Caches the specified number of sequence values into the buffers in the SGA. This speeds access, but all cached numbers are lost when the database is shut down. The default value is 20; maximum value is maxvalue-minvalue.
NOCACHE-Specifies not to preallocate any sequence values.
If you omit both CACHE and NOCACHE, Oracle caches 20 sequence numbers by default.

ORDER / NOORDER:

ORDER- Forces sequence numbers to be output in order of request. In cases where they are used for timestamping, this may be required.
NOORDER - Causes sequence numbers to not automatically be output in order of request.

In most cases, the sequences numbers will be in order anyway, so ORDER will not be required.ORDER is necessary only to guarantee ordered generation if you are using Oracle with the Oracle Real Application Clusters option in parallel mode. If you are using exclusive mode, Oracle sequences numbers are always generated in order.

Create Sequences Examples

CREATE SEQUENCE customers_seq
 START WITH     1000
 INCREMENT BY   1
 NOCACHE
 NOCYCLE;

CREATE SEQUENCE supplier_seq
  MINVALUE 1
  MAXVALUE 999999999999999999999999999
  START WITH 1
  INCREMENT BY 1
  CACHE 20;

ALTER SEQUENCE

Use the ALTER SEQUENCE statement to change the increment, minimum and maximum values, cached numbers, and behavior of an existing sequence. This statement affects only future sequence numbers.

Example

ALTER SEQUENCE supplier_seq
   MAXVALUE 1500;

ALTER SEQUENCE supplier_seq
   CYCLE
   CACHE 5;

DROP SEQUENCE

Use the DROP SEQUENCE statement to remove a sequence from the database.

DROP SEQUENCE supplier_seq;