Sequence

From Oracle FAQ

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:

CREATE SEQUENCE empseq;
 Sequence created.

Selecting from the sequence:

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

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

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;

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	#