Create a sequence

You can use the CREATE SEQUENCE statement to create a sequence. This topic describes how to create a sequence.

Prerequisites

You have the CREATE and ALTER privileges on sequences.

Description

You can use this statement to create a sequence. A sequence is a database object. You can generate a unique integer from a sequence.

If two users increment the same sequence concurrently, the sequence numbers obtained by the two users have an interval. One user can never obtain the sequence number generated by the other user. Sequence numbers are generated independent of the table. Therefore, you can use the same sequence for multiple tables.

After creating a sequence, you can use the CURRVAL pseudocolumn to return the current sequence value or use the NEXTVAL pseudocolumn to return the new increment value in the SQL statement.

Syntax

CREATE SEQUENCE [schema. ] sequence_name
[MINVALUE int_value | NOMINVALUE]
[MAXVALUE int_value | NOMAXVALUE]
[START WITH int_value]
[INCREMENT BY int_value]
[CACHE int_value | NOCACHE]
[ORDER | NOORDER]
[CYCLE | NOCYCLE];

Parameters

Examples

Create a sequence named seq1 and specify the increment to 1. In the following example, return 1 for the first reference to seq1.nextval and return 2 for the second reference. The return value of each subsequent reference is greater than that of the previous reference by 1.

1. Create a sequence named seq1.

   obclient> CREATE SEQUENCE seq1 MINVALUE 1 MAXVALUE 5 NOCYCLE NOORDER NOCACHE;
   Query OK, 0 rows affected
   

   Note

   You can view the sequences that you have created in the USER_SEQUENCES, ALL_SEQUENCES, and DBA_SEQUENCES views.

2. Return 1 for the first reference to seq1.nextval.

   obclient> SELECT seq1.nextval FROM DUAL;
   +---------+
   | NEXTVAL |
   +---------+
   |       1 |
   +---------+
   1 row in set
   

3. Return 2 for the second reference.

   obclient> SELECT seq1.nextval FROM DUAL;
   +---------+
   | NEXTVAL |
   +---------+
   |       2 |
   +---------+
   1 row in set