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 cycle range should be expanded or fewer values should be cached. and retry to resolve the error “ORA-04013: number to CACHE must be less than one cycle”.

The sequence cycle is defined as the difference between the maximum and minimum values of the sequence. The sequence cache value is more than the sequence’s total number of potential values. The sequence cannot create the cache because its possible values are less than the cache size. The error ORA-04013: number to CACHE must be less than one cycle will be thrown.

The sequence cache value should be reduced within the sequence cycle range. The sequence cycle range should be increased by changing the minimum and maximum values. The cyclic sequence values generation should be disabled.

Cause

number to CACHE given is larger than values in a cycle

Action

enlarge the cycle, or cache fewer values

If the sequence cycle range is less than 20, Oracle will throw an error. By default, the sequence cache value is set to 20. The sequence cache’s value exceeds the sequence cycle’s range. The sequence cycle range should be expanded above 20 by default. Modify the default cache value to fit inside the sequence cycle range if necessary.



The Problem

The number of potential values of a sequence cycle are less than those of a sequence cache value. The sequence was unable to generate the number of values required to create the sequence cache. The sequence cache will not be created. The cache value in a sequence cycle should be less than the number of possible values.

The error occurs when the cache size value provided in the Oracle sequence statement is more than total values count in a sequence cycle. The sequence cycle count is the number of distinct values that a sequence can generate.

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

The default sequence cache value in Oracle is 20. If the sequence cycle range is less than the sequence cache value, reduce the sequence cache value that is less than the number of potential values in the sequence cycle.

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

OR

alter sequence MYSEQUENCE cycle cache 3;


Solution 2

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. Modify the sequence MAXVALUE and MINVALUE parameters to increase 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

If your database does not require the cache values, you can disable the cache in the sequence. The NOCACHE command is used in the sequence to disable cache. The sequence will not store values in the cache. The NEXTVAL command delivers the next value that might be created at any time. The query performance may degrade as a result.

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

OR

alter sequence MYSEQUENCE cycle NOCACHE;


Solution 4

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';