Sequence

A sequence is a database object that generates unique numbers, mostly used for primary key values.

One can select the NEXTVAL and CURRVAL from a sequence. Selecting the NEXTVAL will automatically increment the sequence.

History

Sequences were introduced with the Transaction Processing Option in Oracle 6.

Examples

Create a simple sequence:

SQL> CREATE SEQUENCE emp_seq;
 Sequence created.

Selecting from the sequence:

SQL> select emp_seq.nextval from dual;
   NEXTVAL
----------
         1

SQL> select emp_seq.nextval from dual;
   NEXTVAL
----------
         2

Note that NEXTVAL and CURRVAL returns the same value for each row of a select:

SQL> select emp_seq.nextval, emp_seq.currval, emp_seq.nextval, emp_seq.currval from dual;
   NEXTVAL    CURRVAL    NEXTVAL    CURRVAL
---------- ---------- ---------- ----------
         3          3          3          3

SQL> select emp_seq.nextval, emp_seq.currval, emp_seq.nextval, emp_seq.currval 
  2  from (select 1 from dual union all select 2 from dual)
  3  /
   NEXTVAL    CURRVAL    NEXTVAL    CURRVAL
---------- ---------- ---------- ----------
         4          4          4          4
         5          5          5          5

Creating a more complicated sequence:

CREATE SEQUENCE my_sequence
   MINVALUE 1
   MAXVALUE 1000
   START WITH 1
   INCREMENT BY 2
   CACHE 5;

Reset a sequence to a predetermined value, say from 100 to 50:

SQL> ALTER SEQUENCE seq1 INCREMENT BY -50;
SQL> SELECT seq1.nextval FROM dual;
SQL> ALTER SEQUENCE seq1 INCREMENT BY 1;

Remove/delete a sequence:

DROP SEQUENCE my_sequence_name;

LAST_NUMBER

LAST_NUMBER is influenced by the CACHE size. I saw that my LAST_NUMBER was 21 (my cache was 20). When I ran NEXTVAL I was getting a number like 4, then 5. I went up past 20 and my LAST_NUMBER jumped to 41 so everytime the cached numbers ran out another group of cached numbers would be reserved and the LAST_NUMBER field would change, but this has no relation to the CURRVAL.

Also see

• ORA-02287, sequence number not allowed here.
• AutoNumber and Identity columns

Glossary of Terms
A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	#