ORA-04014: descending sequences that CYCLE must specify MINVALUE

ORA-04014: descending sequences that CYCLE must specify MINVALUE error occurs when the sequence is configured to create value in descending order in a cyclic way from maximum value to minimum value and no minimum cutoff value is specified.

You attempted to create an Oracle sequence that is configured to generate values in descending order as well as to run in a cyclic way from highest to minimum value. The Oracle sequence maximum value, on the other hand, is not provided. As a result, the error ORA-04014: descending sequences that CYCLE must specify MINVALUE is thrown.

Cause

sequences that cycle must have their wrap-wrap specified

Action

re-create the sequence, specifying its wrap-value

The problem

The Oracle sequence is created to decrease the value when the NEXTVAL command is run. If the next value is less than the manimum value, the sequence is configured to reset it to the maximum value. The sequence, however, was not provided with the maximum value. As a consequence, the sequence could never reset the value forever.

create sequence mysequence start with 1 increment by -1 MAXVALUE 5 cycle;

Error

Error starting at line : 42 in command -
create sequence mysequence start with 1 increment by -1 MAXVALUE 5 cycle
Error report -
ORA-04014: descending sequences that CYCLE must specify MINVALUE
04014. 00000 -  "descending sequences that CYCLE must specify MINVALUE"
*Cause:    sequences that cycle must have their wrap-wrap specified
*Action:   re-create the sequence, specifying its wrap-value

Solution 1

You create the sequence without specifying the MINVALUE value. The MINVALUE configuration should be added to the sequence so that when the current value approaches the minimum value, the sequence could automatically reset the value.

create sequence mysequence start with 1 increment by -1 MAXVALUE 5 MINVALUE 1 cycle;

OR

alter sequence mysequence increment by -1 MINVALUE 1 cycle;

Solution 2

If the sequence’s minimum value is unknown and unimportant, let the sequence create the value until it reaches the minimum value of the integer datatype. Modify the sequence such that it no longer executes in a cyclical fashion. The NOCYCLIC command configures the sequence not to execute cyclically.

create sequence mysequence start with 1 increment by -1 MAXVALUE 5 nocycle;

OR

alter sequence mysequence NOCYCLE;

Solution 3

The sequence details are critical for understanding the sequence’s present configurations. To determine how to fix the error, the sequence minimum value, maximum value, increment value, and so on must be required. The select query below will provide you all the information you need about the sequence.

select * from user_sequences where sequence_name = 'MYSEQUENCE';