ORA-04015: ascending sequences that CYCLE must specify MAXVALUE

ORA-04015: ascending sequences that CYCLE must specify MAXVALUE oracle error occurs when the sequence is configured to increase value in a cyclic way from minimum value to maximum value and no maximum cutoff value is specified.

Oracle sequence should be configured with minimum value and maximum value to execute in cyclic fashion. You try to create an oracle sequence that generates the next value in cyclic fashion. However, the oracle sequence maximum value is not provided. As a consequence, the error ORA-04015: ascending sequences that CYCLE must specify MAXVALUE 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 increment the value when the NEXTVAL command is run. If the next value exceeds the maximum value, the sequence is configured to reset it to the minimum value. However, the maximum value is not specified in the sequence. As a result, the sequence could never reset the value forever.

create sequence mysequence start with 1 increment by 1 cycle;

Error

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

Solution 1

If the MAXVALUE configuration is missing from your sequence, include it in the Oracle sequence query. The maximum value of the sequence indicates the highest value that the sequence is capable of generating.

create sequence mysequence start with 1 increment by 1 maxvalue 50 cycle;

OR 

alter sequence MYSEQUENCE maxvlaue 50 CYCLE;

Solution 2

If the sequence’s maximum value is unknown and irrelevant, let the sequence generate the value until it hits the integer datatype’s maximum value. Change the sequence so that it does not execute in a circular way. The NOCYCLIC command instructs the sequence not to run in a cyclic fashion.

create sequence mysequence start with 1 increment by 1 nocycle;

OR

alter sequence mysequence NOCYCLE;

Solution 3

The sequence details are important for understanding the current configurations of the sequence. The sequence minimum value, maximum value, increment value, and so on must be required to determine how to fix the error. The following select query will provide you all the information you need about the sequence.

select * from user_sequences where sequence_name = 'MYSEQUENCE';