Oracle 序列 CACHE 值必须小于 CYCLE 值的解决方法

 

之前创建sequence时碰到一个问题, 当我使用了cache时总是提示CACHE 值必须小于 CYCLE 值,查了下文档,找到这么一个公式

 

文档的大概意思是cache的值必须要小于通过这个公式计算得到的值,举例:

这条语句会报错,带入公式有ceil(200-1)/abs(20) = 10,所以cache的最大值只能是10,大于10会报错

create sequence emp_empno_seq
increment by 20
start with 1
maxvalue 200
cycle
cache 20;

 将cache的值修改为<=10,即可正常创建

附:文档解释

Specify how many values of the sequence Oracle preallocates and keeps in memory for faster access. This integer value can have 28 or fewer digits. The minimum value for this parameter is 2. For sequences that cycle, this value must be less than the number of values in the cycle. You cannot cache more values than will fit in a given cycle of sequence numbers. Therefore, the maximum value allowed for CACHE must be less than the value determined by the following formula:

 (CEIL (MAXVALUE - MINVALUE)) / ABS (INCREMENT)