SQL> CREATE SEQUENCE test_seq START WITH 1 INCREMENT BY 1 MAXVALUE 10; Sequence created.
SQL> select test_seq.currval from dual; select test_seq.currval from dual * ERROR at line 1: ORA-08002: sequence TEST_SEQ.CURRVAL is not yet defined in this session SQL> select test_seq.nextval from dual; NEXTVAL ---------- 1 SQL> / NEXTVAL ---------- 2 ... .. . NEXTVAL ---------- 9 SQL> / NEXTVAL ---------- 10 SQL> / select test_seq.nextval from dual * ERROR at line 1: ORA-08004: sequence TEST_SEQ.NEXTVAL exceeds MAXVALUE and cannot be instantiated
1)Increase maximum value:
SQL> alter sequence test_seq maxvalue 15; Sequence altered. SQL> select test_seq.nextval from dual; NEXTVAL ---------- 11 .. . SQL> select test_seq.nextval from dual; NEXTVAL ---------- 14 SQL> select test_seq.nextval from dual; NEXTVAL ---------- 15 SQL> select test_seq.nextval from dual; select test_seq.nextval from dual * ERROR at line 1: ORA-08004: sequence TEST_SEQ.NEXTVAL exceeds MAXVALUE and cannot be instantiated
2)Increment by -1.
SQL> alter sequence test_seq increment by -1; Sequence altered. SQL> select test_seq.nextval from dual; NEXTVAL ---------- 14 SQL> select test_seq.nextval from dual; NEXTVAL ---------- 13 ... .. . SQL> select test_seq.nextval from dual; NEXTVAL ---------- 2 SQL> select test_seq.nextval from dual; NEXTVAL ---------- 1 SQL> select test_seq.nextval from dual; select test_seq.nextval from dual * ERROR at line 1: ORA-08004: sequence TEST_SEQ.NEXTVAL goes below MINVALUE and cannot be instantiated
3) Recycle the sequence starting from minimum value ( 1 ) by:
alter sequence cycle
SQL>select test_seq.nextval from dual; NEXTVAL ---------- 15 SQL> / select test_seq.nextval from dual * ERROR at line 1: ORA-08004: sequence TEST_SEQ.NEXTVAL exceeds MAXVALUE and cannot be instantiated SQL> select SEQUENCE_NAME,MIN_VALUE,MAX_VALUE, INCREMENT_BY,CACHE_SIZE,CYCLE_FLAG from dba_sequences where SEQUENCE_NAME='TEST_SEQ'; SEQUENCE_NAM MIN_VALUE MAX_VALUE INCREMENT_BY CACHE_SIZE C ------------ ---------- ---------- ------------ ---------- - TEST_SEQ 1 15 1 20 N SQL> alter sequence TEST_SEQ cycle cache 10; Sequence altered. SQL> select test_seq.nextval from dual ; NEXTVAL ---------- 1