Sequences by Example

==========================
Create Sequence
==========================
CREATE SEQUENCE my_sequence
       INCREMENT BY 1
       START WITH 1
       NOMAXVALUE
       NOCYCLE
       CACHE 10;


==========================
Fetch Sequence Next Value
==========================
SELECT my_sequence.NEXTVAL FROM DUAL;
1

==========================
See Sequence Current Number
==========================
SELECT my_sequence.CURRVAL FROM DUAL;
1

==========================
Set Sequence increment
==========================
ALTER SEQUENCE my_sequence INCREMENT BY 20
SELECT my_sequence.NEXTVAL FROM DUAL;
21

==========================
Set Sequence to New Init Value
==========================
ALTER SEQUENCE my_sequence  START WITH 101
ORA-02283: cannot alter starting sequence number

The solution it to advance sequence to N-1 number manually.
ALTER SEQUENCE my_sequence  INCREMENT BY 20
SELECT my_sequence.CURRVAL FROM DUAL;
22

ALTER SEQUENCE my_sequence  INCREMENT BY 77
SELECT my_sequence.NEXTVAL FROM DUAL;
99

ALTER SEQUENCE my_sequence  INCREMENT BY 1
100

ALTER SEQUENCE my_sequence  INCREMENT BY -99
SELECT my_sequence.NEXTVAL FROM DUAL;
1

What is advancing the sequence too low?

ALTER SEQUENCE my_sequence  INCREMENT BY -99999
SELECT my_sequence.NEXTVAL FROM DUAL;
ORA-08004: sequence MY_SEQUENCE.NEXTVAL goes below MINVALUE and cannot be instantiated

==========================
Handle MAXVALUE option
==========================
Without CYCLE Option.
DROP SEQUENCE my_sequence;
CREATE SEQUENCE my_sequence
       INCREMENT BY 1
       START WITH 1
       MAXVALUE 5
       NOCYCLE
       NOCACHE;

SELECT my_sequence.NEXTVAL FROM DUAL;

1

SELECT my_sequence.NEXTVAL FROM DUAL;

2

SELECT my_sequence.NEXTVAL FROM DUAL;

3

SELECT my_sequence.NEXTVAL FROM DUAL;

4

SELECT my_sequence.NEXTVAL FROM DUAL;

5

SELECT my_sequence.NEXTVAL FROM DUAL; 

ORA-08004: sequence MY_SEQUENCE.NEXTVAL exceeds MAXVALUE and cannot be instantiated

ALTER SEQUENCE my_sequence  INCREMENT BY -4

SELECT my_sequence.NEXTVAL FROM DUAL;

1

Or, increase MAXVALUE

ALTER SEQUENCE my_sequence MAXVALUE 20;

With CYCLE Option.

DROP SEQUENCE my_sequence;

CREATE SEQUENCE my_sequence

       INCREMENT BY 1

       START WITH 1

       MAXVALUE 5

       CYCLE

       NOCACHE;

SELECT my_sequence.NEXTVAL FROM DUAL;

1

SELECT my_sequence.NEXTVAL FROM DUAL;

2

SELECT my_sequence.NEXTVAL FROM DUAL;

3

SELECT my_sequence.NEXTVAL FROM DUAL;

4

SELECT my_sequence.NEXTVAL FROM DUAL;

5

SELECT my_sequence.NEXTVAL FROM DUAL;  

1

==========================
Invalid Options
==========================
 - Create sequence NOMAXVALUE + CYCLE

CREATE SEQUENCE my_sequence

       INCREMENT BY 1

       START WITH 1

       NOMAXVALUE

       CYCLE

       NOCACHE

ORA-04015: ascending sequences that CYCLE must specify MAXVALUE

==========================

Invalid Options

==========================

SELECT * FROM USER_SEQUENCES WHERE sequence_name = 'MY_SEQUENCE';

SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY CYCLE_FLAG ORDER_FLAG CACHE_SIZE LAST_NUMBER
------------- --------- --------- ------------ ---------- ---------- ---------- -----------
MY_SEQUENCE                             1         20            1 Y          N                   0           1