Oracle Sequences DDL

CREATE Sequence

Sequences allow for automatic generation of sequential non-repeating or repeating integer values for use in keys or elsewhere. Sequences can be positive or negative. Also a number of values can be cached for performance. However, in the case of a database shut down these cached values are lost. If a statement that references a sequence is rolled back that sequence number will be lost.

• sequence_name - can be preceded by a username.
• INCREMENT BY - positive values ascend and negative values descend.
• START WITH - starting integer.
• MINVALUE - ascending defaults to 1 and descending defaults to 10^-27.
• MAXVALUE - descending defaults to 1 and ascending defaults to 10²⁷.
• CYCLE - automatically recycles to MINVALUE when MAXVALUE reached for ascending sequences and visa versa for descending sequences.
• CACHE - caches the specified number of sequences into the SGA buffers. Note that all cached numbers are lost if the database is shut down. Default value is 20 and maximum value is MAXVALUE - MINVALUE.
• ORDER - forces sequence numbers to be output in order of request. In cases where sequences are used for time stamping ordering may be required. In most cases sequence numbers will be ordered anyway and ORDER will not be required.
• CURRVAL & NEXTVAL - Sequences can not be accessed directly and can only be retrieved using the CURRVAL and NEXTVAL pseudocolumns. Also a sequence must be first accessed with NEXTVAL before CURRVAL can be used.
  • Uses
    • INSERT statement VALUES clause.
    • SELECT subclause of a SELECT command.
    • SET clause of an UPDATE command.
  • Use not allowed
    • In a subquery.
    • In a view or snapshot query.
    • With a DISTINCT clause.
    • With GROUP BY or ORDER BY clauses.
    • In a SELECT statement in combination with another SELECT command.
    • In a WHERE clause.
    • In the DEFAULT column value in a CREATE TABLE or ALTER TABLE command.
    • In a CHECK constraint.

ALTER Sequence

Use ALTER SEQUENCE to change minimum, maximum, caching and increment parameter values. Note that future sequence values only are affected by ALTER SEQUENCE. START WITH can only be altered by dropping and recreating the sequence.

DROP Sequence