ORA-04013: number to CACHE must be less than one cycle

ORA-04013: number to CACHE must be less than one cycle error occurs if the number to cache specified in a create sequence statement exceeds the number of values in a sequence cycle. The sequence cache is used to generate and reserve a small number of items in cache memory in order to serve SQL queries quickly. The sequence cycle is a collection of all the possible values generated by the sequence. If the cache size exceeds the sequence cycle, the sequence will be unable to create all cache values. The sequence cycle range should be extended or fewer values stored in cache.

The sequence cycle is defined as the difference between the sequence’s maximum and minimum values. The sequence cycle includes all of the sequence’s unique possible values. The sequence cache value should be less than the total number of possible values for the sequence. The cache cannot be created because the sequence’s possible values are smaller than the cache size. In this case, the error ORA-04013: number to CACHE must be less than one cycle will be thrown.

The following measures should be taken to fix this error. Within the sequence cycle range, the sequence cache value should be reduced. By altering the minimum and maximum values, the sequence cycle range should be expanded. The generation of cyclic sequence values should be deactivated so that there is no restriction on sequence values.

Cause

number to CACHE given is larger than values in a cycle

Action

enlarge the cycle, or cache fewer values

Oracle will give an error if the sequence cycle range is less than 20. The sequence cache value is set to 20 by default. The value of the sequence cache exceeds the range of the sequence cycle. By default, the sequence cycle range should be expanded beyond 20. If required, change the default cache value to fit inside the sequence cycle range.

The Problem

A sequence cycle’s possible values are less than those of a sequence cache value. The sequence was unable to generate the appropriate amount of values to establish the sequence cache. There will be no creation of the sequence cache. In a sequence cycle, the cache value should be less than the number of possible values.

When the cache size value specified in the Oracle sequence statement exceeds the total number of values in a sequence cycle, the error occurs. The amount of unique values that a sequence may generate is represented by the sequence cycle count.

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

OR

alter sequence MYSEQUENCE MAXVALUE 5 cycle;

Error

create sequence mysequence start with 1 increment by 1 MAXVALUE 5 cycle
Error report -
ORA-04013: number to CACHE must be less than one cycle
04013. 00000 -  "number to CACHE must be less than one cycle"
*Cause:    number to CACHE given is larger than values in a cycle
*Action:   enlarge the cycle, or cache fewer values

Solution 1

Oracle’s default sequence cache value is 20. The sequence cache cannot be created if a sequence is created with a cycle range of less than 20. The number of sequence cycle possible values is less than the default sequence cache value. The default sequence cache value should be reduced to a value smaller than the sequence cycle range value. In this situation, the sequence can generate a large number of values, necessitating the creation of the sequence cache.

create sequence MYSEQUENCE start with 1 increment by 1 MAXVALUE 5 cache 3 cycle;

OR

alter sequence MYSEQUENCE cycle cache 3;

Solution 2

Alternatively, the sequence cycle range should be expanded beyond the sequence cache value. The sequence cycle range is the difference between the minimum and maximum value. The sequence minimum and maximum parameters should be changed to expand the sequence cycle range. The sequence’s minimum and maximum values should be reduced or increased, respectively. Change the sequence MAXVALUE and MINVALUE parameters to enhance the sequence cycle range value.

create sequence mysequence start with 1 increment by 1 MAXVALUE 25 cycle;

OR

alter sequence MYSEQUENCE MAXVALUE 25 cycle;

Solution 3

You can deactivate the cache in the sequence if your database does not require the cache values. The NOCACHE command is used in the sequence to disable cache. There will be no values cached by the sequence. The NEXTVAL command returns the next value generated at any time. As a result, query performance may suffer.

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

OR

alter sequence MYSEQUENCE cycle NOCACHE;

Solution 4

You can deactivate the cycle in the sequence if you don’t need to generate the values in cycle. The NOCYCLE command is used in the sequence to disable the values generated in cycle. All the values generated in the sequence will be unique. There will be no limit to generate the sequence values. The NEXTVAL command will return a new value for all the time.

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

OR

alter sequence MYSEQUENCE CACHE NOCYCLE;

Solution 5

The sequence current maximum, minimum, and cache values must be known in order to alter the sequence and resolve the error. The SQL query below will return the sequence information required to comprehend the current value. The current cache value should be decreased to fit inside the sequence range. Otherwise, the sequence present minimum and maximum values should be adjusted to broaden the sequence cycle range.

select * from user_sequences where sequence_name = 'MYSEQUENCE';