Oracle Sequence Cycle vs Nocycle Difference and Comparison

advertisements

_____________________________________________________________________________________________________________________

The cycle option repeats the same set of sequence values once it reaches the max value. The next value will be reset to minimum value once it reaches the max value. The cycle option will end up with error if you are using this sequence for the primary key values as it won’t allow the duplicates. 

The oracle sequence by default creates with nocycle option. The nocycle option will give you an error message when you try to fetch next value after reaching the max value. Nocycle option will not generate more values once it reaches max value. Nocycle option is safe for primary key values. 

See some examples below.

SQL> create sequence seq_norm start with 1 maxvalue 100 increment by 25 cycle nocache;

Sequence created.

SQL> select seq_norm.nextval from dual;

   NEXTVAL

----------

         1

SQL> /

   NEXTVAL

----------

        26

SQL> /

   NEXTVAL
----------
        51

SQL> /

   NEXTVAL
----------
        76

SQL> /                  ====== Repeating the start value after a cycle

   NEXTVAL
----------
         1

By default the Oracle sequence create with nocycle option.

SQL> create sequence seq_norm start with 1 maxvalue 100 increment by 25 ;
Sequence created.
SQL>  select seq_norm.nextval from dual;
   NEXTVAL
----------
         1
SQL> /
 
NEXTVAL
----------
        26
SQL> /

   NEXTVAL
----------
        51

SQL> /
   NEXTVAL
----------
        76
SQL> /
 select seq_norm.nextval from dual
*
ERROR at line 1:
ORA-08004: sequence SEQ_NORM.NEXTVAL exceeds MAXVALUE and cannot be instantiated

_____________________________________________________________________________________________________________________

Website Stats