EnterpriseDB: DEFAULTS Oracle Tips by Burleson Consulting

When you create a sequence, you inherit several defaults.  By default, a sequence begins at 1, is an increment sequence, is not cached, will not cycle, and uses the default max value.  Some options impact other option defaults.  For example, set a negative increment and you will start at MAXVALUE instead of 1.

CREATE SEQUENCE
CREATE [ TEMPORARY | TEMP ] SEQUENCE <sequence name>
[ INCREMENT [ BY ] <increment> ]
[ MINVALUE <minimum value>| NO MINVALUE ]
[ MAXVALUE <maximum value>| NO MAXVALUE ]
[ START [ WITH ] <start value>]
[ CACHE <cache size>]
[ [ NO ] CYCLE ]

Create a sequence named "seq1" that is an increment sequence, skips one value on each call and starts at 1.

CREATE SEQUENCE seq1 START WITH 1;

Create a sequence named "seq1" that is an increment sequence, skips two values on each call and starts at 15.

CREATE SEQUENCE seq1 INCREMENT 2 START WITH 15;

Create a sequence named "seq1" that is a decrement sequence, skips -1 value on each call, starts at -99 and cycles values.

CREATE SEQUENCE seq1 INCREMENT -1 START WITH -99 CYCLE;

Create a sequence named "seq1" that is an increment sequence, skips one value on each call, starts at 1, does not cycle and caches 20 values.

CREATE SEQUENCE seq1 CACHE 20;

To use the sequence, you select either the current value or the next value from a sequence.  To select the current value, use sequence_name.currval and to select the next value, use sequence_name.nextval.

Example:

We can select next value of seq1.

SELECT seq1.nextval FROM dual;

edb=# CREATE SEQUENCE seq1 CACHE 20;

CREATE SEQUENCE

edb=# SELECT seq1.nextval FROM dual;

 nextval
---------
       1

(1 row)

edb=# SELECT seq1.nextval FROM dual;

 nextval
---------
       2

(1 row)

edb=# SELECT seq1.nextval FROM dual;

 nextval
---------
       3

(1 row)

edb=# 

We can select the current value of seq1.

SELECT seq1.currval FROM dual;

edb=# SELECT seq1.currval FROM dual;

 currval
---------
       3

(1 row)

edb=# SELECT seq1.currval FROM dual;

 currval
---------
       3

(1 row)

edb=#

ALTER SEQUENCE

Alter a sequence named "seq1" and set it to cycle and cache 20 values.

ALTER SEQUENCE seq1 CYCLE CACHE 20;

Alter a sequence named "seq1", set it to decrement 5 on each call and restart it at 100.

ALTER SEQUENCE seq1 INCREMENT -5 RESTART WITH 100;

GRANT SEQUENCE

Grant access to a sequence named "seq1" to database user tom.

GRANT SELECT ON seq1 TO tom;

Grant access to a sequence named "seq1" to the special user "public" which means everyone will have access.

GRANT SELECT ON seq1 TO PUBLIC;

REVOKE SEQUENCE

Since we granted select to everyone (by granting select to public), take away specific access from tom.  tom will still be able to use the sequence through grants to public.

REVOKE SELECT FROM tom;

DROP SEQUENCE

Drop the sequence named "seq1".

DROP SEQUENCE seq1;

This is an excerpt from the book "EnterpriseDB: The Definitive Reference" by Rampant TechPress.