Oracle Sequence Cycle vs Nocycle Difference and Comparison

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
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google