CREATE SEQUENCE
Purpose
A sequence is a database object used to produce unique integers, which are commonly used to populate a synthetic primary key column in a table. The sequence number always increases, typically by 1, and each new entry is placed on the right-most leaf block of the index.
Use the CREATE
SEQUENCE
statement to create a sequence 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.
See Also:
-
Pseudocolumns for more information on using the
CURRVAL
andNEXTVAL
-
"How to Use Sequence Values" for information on using sequences
-
ALTER SEQUENCE or DROP SEQUENCE for information on modifying or dropping a sequence
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::=
Semantics
IF NOT EXISTS
Specifying IF NOT EXISTS
has the following effects:
-
If the sequence does not exist, a new sequence is created at the end of the statement.
-
If the sequence exists, this is the sequence you have at the end of the statement. A new one is not created because the older one is detected.
Using IF EXISTS
with CREATE
results in ORA-11543: Incorrect IF NOT EXISTS clause for CREATE statement
.
schema
Specify the schema to contain the sequence. If you omit schema
, then Oracle Database creates the sequence in your own schema.
sequence
Specify the name of the sequence to be created. The name must satisfy the requirements listed in "Database Object Naming Rules".
If you specify none of the clauses INCREMENT
BY
through GLOBAL
, 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 specifyNOMAXVALUE
. For descending sequences, omit theMINVALUE
parameter or specify theNOMINVALUE
. -
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 theMINVALUE
parameter. Also specifyNOCYCLE
. 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
andMINVALUE
parameters. Also specifyCYCLE
.
SHARING
This clause applies only when creating a sequence in an application root. This type of sequence is called an application common object and it can be shared with the application PDBs that belong to the application root. To determine how the sequence is shared, specify one of the following sharing attributes:
-
METADATA
- A metadata link shares the sequence’s metadata, but its data is unique to each container. This type of sequence is referred to as a metadata-linked application common object. -
DATA
- A data link shares the sequence, and its data is the same for all containers in the application container. Its data is stored only in the application root. This type of sequence is referred to as a data-linked application common object. -
NONE
- The sequence is not shared.
If you omit this clause, then the database uses the value of the DEFAULT_SHARING
initialization parameter to determine the sharing attribute of the sequence. If the DEFAULT_SHARING
initialization parameter does not have a value, then the default is METADATA
.
You cannot change the sharing attribute of a sequence after it is created.
See Also:
-
Oracle Database Reference for more information on the
DEFAULT_SHARING
initialization parameter -
Oracle Database Administrator’s Guide for complete information on creating application common objects
INCREMENT BY
Specify the interval between sequence numbers. This integer value can be any positive or negative integer, but it cannot be 0. This value can have 28 or fewer digits for an ascending sequence and 27 or fewer digits for a descending sequence. The absolute of this value must be less than the difference of MAXVALUE
and MINVALUE
. If this value is negative, then the sequence descends. If the value is positive, then the sequence ascends. If you omit this clause, then the interval defaults to 1.
START WITH
Specify the first sequence number to be generated. Use this clause to start an ascending sequence at a value greater than its minimum or to start a descending sequence at a value less than its maximum. 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 for positive values and 27 or fewer digits for negative values.
Note:
This value is not necessarily the value to which an ascending or descending cycling sequence cycles after reaching its maximum or minimum value, respectively.
MAXVALUE
Specify the maximum value the sequence can generate. This integer value can have 28 or fewer digits for positive values and 27 or fewer digits for negative values. 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 1028-1 for an ascending sequence or -1 for a descending sequence. This is the default.
MINVALUE
Specify the minimum value of the sequence. This integer value can have 28 or fewer digits for positive values and 27 or fewer digits for negative values. 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 -(1027 -1) for a descending sequence. This is the default.
CYCLE
Specify CYCLE
to indicate that the sequence continues to generate values after reaching either its maximum or minimum value. After an ascending sequence reaches its maximum value, it generates its minimum value. After a descending sequence reaches its minimum, it generates its maximum value.
NOCYCLE
Specify NOCYCLE
to indicate that the sequence cannot generate more values after reaching its maximum or minimum value. This is the default.
CACHE
Specify how many values of the sequence the database preallocates 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, then 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.
Note:
Oracle recommends using the CACHE
setting to enhance performance if you are using sequences in an Oracle Real Application Clusters environment.
NOCACHE
Specify NOCACHE
to indicate that values of the sequence are not preallocated. If you omit both CACHE
and NOCACHE
, then the database caches 20 sequence numbers by default.
ORDER
Specify ORDER
to guarantee that sequence numbers are generated in order of request. This clause is useful if you are using the sequence numbers as timestamps. Guaranteeing order is usually not important for sequences used to generate primary keys.
NOORDER
Specify NOORDER
if you do not want to guarantee sequence numbers are generated in order of request. This is the default.
KEEP
Specify KEEP
if you want NEXTVAL
to retain its original value during replay for Application Continuity. This behavior will occur only if the user running the application is the owner of the schema containing the sequence. This clause is useful for providing bind variable consistency at replay after recoverable errors. Refer to Oracle Database Development Guide for more information on Application Continuity.
NOKEEP
Specify NOKEEP
if you do not want NEXTVAL
to retain its original value during replay for Application Continuity. This is the default.
Note:
The KEEP
and NOKEEP
clauses apply only to the owner of the schema containing the sequence. You can control whether NEXTVAL
retains its original value for other users during replay for Application Continuity by granting or revoking the KEEP
SEQUENCE
object privilege on the sequence. Refer to Table 18-4 for more information on the KEEP
SEQUENCE
object privilege.
SCALE
Use SCALE
to create a scalable sequence. A scalable sequence adds a 5 digit prefix to the sequence. The prefix is made up of a 2 digit instance offset concatenated to a 3 digit session offset as follows:
[(instance id % 100) ] || [session id % 1000]
The final sequence number is in the format prefix || zero-padding || sequence
, where the amount of padding depends on the maximum width of the sequence.
Prior to Release 23, a scalable sequence would have a leading "1" as part of the instance offset:
SELECT mysequence.nextval FROM DUAL; NEXTVAL ---------- 101213001
In Release 23, this same scalable sequence will have the leading "1" of the instance offset removed:
SELECT mysequence.nextval FROM DUAL; NEXTVAL ---------- 1213001
Note:
Starting with Oracle Database Release 23 any newly created scalable sequences will have the leading "1" of the instance offset removed. Scalable sequences created prior to Release 23 will retain the leading '1' of the instance offset.
When you use SCALE
it is highly recommended that you not use ORDER
simultaneously on the sequence.
EXTEND
Specifying EXTEND
with SCALE
causes the sequence number to be left padded with zeros to its maximum length, then the prefix concatenated, so the final sequence number has 6 more digits than the MAXVALUE
setting.
NOEXTEND
The default setting for the SCALE
clause is NOEXTEND
. With the NOEXTEND
setting, the generated sequence values are at most as wide as the maximum number of digits in the sequence MAXVALUE
setting.
NOEXTEND
is the default setting for the SCALE
clause. With the NOEXTEND
setting, the generated sequence values are at most as wide as the maximum number of digits in the sequence (maxvalue/minvalue)
. This setting is useful for integration with existing applications where sequences are used to populate fixed width columns.
NOSCALE
The default attribute for a sequence is NOSCALE
, but you can also specify it explicitly to disable sequence scalability..
SHARD
For complete semantics on the SHARD
clause please refer to the SHARD
clause of the ALTER SEQUENCE statement.
SESSION
Specify SESSION
to create a session sequence, which is a special type of sequence that is specifically designed to be used with global temporary tables that have session visibility. Unlike the existing regular sequences (referred to as "global" sequences for the sake of comparison), a session sequence returns a unique range of sequence numbers only within a session, but not across sessions. Another difference is that session sequences are not persistent. If a session goes away, so does the state of the session sequences that were accessed during the session.
Session sequences must be created by a read-write database but can be accessed on any read-write or read-only databases (either a regular database temporarily open read-only or a standby database).
The CACHE
, NOCACHE
, ORDER
, or NOORDER
clauses are ignored when specified with the SESSION
clause.
See Also:
Oracle Data Guard Concepts and Administration for more information on session sequences
Examples
Creating a Sequence: Example
The following statement creates the sequence customers_seq
in the sample schema oe
. This sequence could be used to provide customer ID numbers when rows are added to the customers
table.
CREATE SEQUENCE customers_seq START WITH 1000 INCREMENT BY 1 NOCACHE NOCYCLE;
The first reference to customers_seq.nextval
returns 1000. The second returns 1001. Each subsequent reference will return a value 1 greater than the previous reference.