Sequence is a schema object that can generate unique sequential values that to integers. The sequence values are often used for Primary Keys and Unique Keys. Sequence numbers are generated independently of tables. So, the same sequence can be used for one or for multiple tables. When a sequence number is generated, the sequence is incremented independent of the transaction is committed or rolled back.
Sequences can be shared between two users, between two servers of the same database, but can not be shared between two databases. Multiple users can concurrently increment the same sequence and in this process one user can never acquire the sequence number generated by another user.
The different options for creating a Sequence are:
- START WITH
- INCREMENT BY
- MAXVALUE/ NOMAXVALUE
- CYCLE/ NOCYCLE
- CACHE/ NOCACHE
INCREMENT BY: It can be any positive or negative integer, but it cannot be 0. This value can have 28 or fewer digits. This value must be less than the difference of MAXVALUE and MINVALUE. For ascending sequence default value is 1 and for descending sequence default value is -1.
START WITH: For ascending Sequences, the default value is the minimum value of the Sequence. For descending Sequences, the default value is the maximum value of the Sequence. This integer value can have 28 or fewer digits.
MAXVALUE: It can have 28 or fewer digits. MAXVALUE must be equal to or greater than START WITH and must be greater than MINVALUE.
NOMAXVALUE: Specify NOMAXVALUE to indicate a maximum value of POWER(10,27) for an ascending sequence or -1 for a descending sequence. NOMAXVALUE is the default, if MAXVALUE is not mentioned.
MINVALUE: It can have 28 or fewer digits. MINVALUE must be less than or equal to START WITH and must be less than MAXVALUE.
NOMINVALUE: Specify NOMINVALUE to indicate a minimum value of 1 for an ascending Sequence or POWER(-10,26) for a descending Sequence. This is the default.
CACHE: Specify how many values of the Sequence, the database pre-allocates and keeps in memory for faster access. This integer value can have 28 or fewer digits. The minimum value for this parameter is 2. For Sequences that cycle, this value must be less than the number of values in the cycle. You cannot cache more values than will fit in a given cycle of sequence numbers. Therefore, the maximum value allowed for CACHE must be less than the value determined by the following formula:
(CEIL (MAXVALUE - MINVALUE)) / ABS (INCREMENT)
If a system failure occurs, all cached sequence values that have not been used in committed DML statements are lost. The potential number of lost values is equal to the value of the CACHE parameter.
NOCACHE: Specify NOCACHE to indicate that values of the sequence are not pre-allocated. If you omit both CACHE and NOCACHE, the database caches 20 sequence numbers by default.
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 continues 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, specify NOCYCLE. For an ascending sequence, specify a value for the MAXVALUE parameter. For a descending sequence, specify a value for the MINVALUE parameter. If you do not specify MINVALUE, then it defaults to NOMINVALUE, which is the value 1.
CREATE SEQUENCE statement to create a sequence. 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.
CREATE SEQUENCE SampleSeq1 INCREMENT BY 1 START WITH 0 MINVALUE 0 MAXVALUE 5 NOCACHE CYCLE; Sequence created.
You can create a Sequence by any of these options or none. The option which is not mentioned takes default values. The default values of the options are as follows.
- INCREMENT BY defaults to 1.
- START WITH defaults to MINVALUE if Sequence is ascending and defaults to MAXVALUE if Sequence is descending.
- NOMAXVALUE is the default if MAXVALUE is not mentioned.
- NOMINVALUE is the default if MINVALUE is not mentioned.
- Default is NOCYCLE
- CACHE defaults to 20
CREATE SEQUENCE SampleSeq2; Sequence created.
CREATE SEQUENCE SampleSeq3 INCREMENT BY 2 START WITH 10 MINVALUE 0; Sequence created.
A Sequence can be increment sequence or decrement sequence. INCREMENT BY can accept integer values which can be positive or negative, but can not be fraction.
--Creating a decrement sequence CREATE SEQUENCE SampleSeq4 INCREMENT BY -1; Sequence created.
Sequences are schema objects they hence, are stored in USER_OBJECTS. The details of every Sequence are stored in another metadata called as USER_SEQUENCES.
SELECT OBJECT_NAME, OBJECT_TYPE FROM USER_OBJECTS WHERE OBJECT_TYPE = 'SEQUENCE'; OBJECT_NAME OBJECT_TYPE -------------------- ----------------------- SAMPLESEQ2 SEQUENCE SAMPLESEQ1 SEQUENCE SAMPLESEQ4 SEQUENCE SAMPLESEQ3 SEQUENCE
SELECT SEQUENCE_NAME, MIN_VALUE, MAX_VALUE, INCREMENT_BY, LAST_NUMBER FROM USER_SEQUENCES; SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY LAST_NUMBER -------------------- ---------- ---------- ------------ ----------- SAMPLESEQ1 0 5 1 0 SAMPLESEQ2 1 1.0000E+28 1 1 SAMPLESEQ3 0 1.0000E+28 2 10 SAMPLESEQ4 -1.000E+27 -1 -1 -1
Use the DROP SEQUENCE statement to remove a sequence from the database. You must have DROP SEQUENCE privilege to drop sequences in your own schema or DROP ANY SEQUENCE system privilege to drop sequences in other schemas.
DROP SEQUENCE SampleSeq1; Sequence dropped.