Create a sequence

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

Syntax

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

Parameters

Examples

Create a sequence named seq1 and specify the increment step to 2. In the following example, 1 is returned for the first reference to seq1.nextval, and 2 is returned for the second reference. The value returned for each subsequent reference increments 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 sequence that you have created in the USER_SEQUENCES, ALL_SEQUENCES, and DBA_SEQUENCES views.

2. Initiate the first reference to seq1.nextval.

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

3. Initiate the second reference.

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