ORA-04006: START WITH cannot be less than MINVALUE

ORA-04006: START WITH cannot be less than MINVALUE error occurs if the sequence starts with a value less than minimum value of the sequence. You have created a sequence that starts with a value less than the sequence’s minimum value.

The minimum value of the sequence is defined as the lowest value that the sequence can create. The sequence shall start with a value that is more than or equal to the minimum value. If the sequence begins with a value smaller than the sequence’s minimum value, the sequence will throw an error ORA-04006: START WITH cannot be less than MINVALUE.

Cause

the given starting value is less than MINVALUE

Action

make sure that the starting value is >= MINVALUE



The Problem

The sequence is designed to generate values ranging from the smallest to the largest. The descending order sequence will begin with the highest value and finish with the lowest value. It is not required to begin the sequence with the smallest value. The START WITH keyword can be used to define the starting value of a sequence.

Configuring the starting value to be less than the minimum value has less relevance. The ascending order sequence’s start value cannot be less than the minimum value.

create sequence mysequence start with 5 MINVALUE 10 increment by 1;

Error

Error starting at line : 11 in command -
create sequence mysequence start with 5 MINVALUE 10 increment by 1
Error report -
ORA-04006: START WITH cannot be less than MINVALUE
04006. 00000 -  "START WITH cannot be less than MINVALUE"
*Cause:    the given starting value is less than MINVALUE
*Action:   make sure that the starting value is >= MINVALUE


Solution 1

Change the sequence’s start value, minimum value, or both if sequence starts with a value less than the minimum value. The sequence’s start value should be larger than the sequence’s minimum value.

create sequence mysequence start with 50 MINVALUE 10 increment by 1;


Solution 2

The sequence details are important for comprehending the sequence’s present configurations. To decide how to fix the error, the sequence minimum value, maximum value, increment value, and so on must be necessary. The sequence’s start value may be found in the LAST NAME column of the user sequences table. The select query below will provide you all the information you need about the sequence.

select * from user_sequences where sequence_name = 'MYSEQUENCE';


Solution 3

By default, when an ascending order sequence is created, the minimum value is set to 1. The ascending order sequence must start with a value higher than or equal to 1. The following example illustrates the error since it starts with 0 and increases by 1.

The ascending order sequence’s default minimum and maximum values are 1 and 999999999999999999999999999. The ascending order sequence’s start value should be greater than or equal to 1, or the minimum value should be decreased.

create sequence mysequence start with 0 increment by 1;

Error

Error starting at line : 2 in command -
create sequence mysequence start with 0 increment by 1
Error report -
ORA-04006: START WITH cannot be less than MINVALUE
04006. 00000 -  "START WITH cannot be less than MINVALUE"
*Cause:    the given starting value is less than MINVALUE
*Action:   make sure that the starting value is >= MINVALUE

Solution

create sequence mysequence increment by 1;

The sequence minimum and maximum values are 1 and 999999999999999999999999999. The default start value of the sequence is 1.