ORA-04008: START WITH cannot be more than MAXVALUE error occurs if the sequence starts with a value larger than sequence maximum value. You have created a sequence that starts with a value more than the sequence’s maximum value.

The START WITH keyword can be used to provide the sequence’s starting value while creating the sequence. A sequence’s maximum value is defined as the highest value that it can create. The sequence should begin with a value that is less than the sequence’s maximum value. Otherwise, the error ORA-04008: START WITH cannot be more than MAXVALUE will be thrown.

Cause

the starting value would be larger than MAXVALUE

Action

make sure that the starting value is less than MAXVALUE



The Problem

The sequence range can be defined as the difference between the sequence’s minimum and maximum values. It is not always necessary for the sequence to begin with the lowest number.

The START WITH keyword is used to specify the sequence’s starting value. If the start value is greater than the maximum value of the sequence, the sequence cannot begin with the specified value.

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

Error

Error starting at line : 11 in command -
create sequence mysequence start with 10 MAXVALUE 5 increment by 1
Error report -
ORA-04008: START WITH cannot be more than MAXVALUE
04008. 00000 -  "START WITH cannot be more than MAXVALUE"
*Cause:    the starting value would be larger than MAXVALUE
*Action:   make sure that the starting value is less than MAXVALUE


Solution 1

If the sequence begins with a value greater than the maximum value, change the sequence’s start value, maximum value, or both. The start value of the sequence should be smaller than the maximum value of the sequence.

create sequence mysequence start with 10 MAXVALUE 50 increment by 1


Solution 2

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 start value of the sequence may be found in the user sequences table’s LAST NAME column. The following select query will provide you all the information you need about the sequence.

select * from user_sequences where sequence_name = 'MYSEQUENCE';


Solution 3

If a descending order sequence is created, the maximum value is set to -1 by default. The descending order should begin with a value less than or equal to -1. The following example demonstrates the error since it starts with 1 and decrements by 1.

The descending order sequence’s default minimum and maximum values are -999999999999999999999999999 and -1. The descending order sequence’s start value should be less than or equal to -1, or the maximum value should be increased.

create sequence mysequence start with 1 increment by -1;

Error

Error starting at line : 2 in command -
create sequence mysequence start with 1 increment by -1
Error report -
ORA-04008: START WITH cannot be more than MAXVALUE
04008. 00000 -  "START WITH cannot be more than MAXVALUE"
*Cause:    the starting value would be larger than MAXVALUE
*Action:   make sure that the starting value is less than MAXVALUE

Solution

create sequence mysequence increment by -1;

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



Leave a Reply