当前位置: 首页 > Oracle, oracle 11g > 正文

Oracle 更改序列属性遇到ORA-04007 错误

今天,开发人员要修改一个序列,之前序列的初始值是1000,最大值是9999,步长是1,循环使用,因为现在节目有点多,造成了数据重复的现象,要求把初始值和最大值修改为10000和99999。

像这种修改序列,最小值比原来最大值还大的情况,是不能直接修改的,否则就会遇到ORA-04007错误。

chgshs@IVLDB> alter sequence SQ_PLAN_PGM_CODE minvalue 10000 maxvalue 99999;
alter sequence SQ_PLAN_PGM_CODE minvalue 10000 maxvalue 99999
*
ERROR at line 1:
ORA-04007: MINVALUE cannot be made to exceed the current value

修改序列的最大值是不会遇到问题的,修改最小值就会收到当前值的限制,序列的初始值也就是最小值不能大于序列的当前值,否则就会遇到这个错误。

chgshs@IVLDB> alter sequence SQ_PLAN_PGM_CODE MAXVALUE 99999;

Sequence altered.
chgshs@IVLDB> alter sequence SQ_PLAN_PGM_CODE minvalue 10000;
alter sequence SQ_PLAN_PGM_CODE minvalue 10000
*
ERROR at line 1:
ORA-04007: MINVALUE cannot be made to exceed the current value

下面用DBMS_METADATA包来看一下这个序列的信息。

chgshs@IVLDB> set long 10000
chgshs@IVLDB> select dbms_metadata.get_ddl('SEQUENCE','SQ_PLAN_PGM_CODE','CHGSHS') FROM DUAL;

DBMS_METADATA.GET_DDL('SEQUENCE','SQ_PLAN_PGM_CODE','CHGSHS')
--------------------------------------------------------------------------------

   CREATE SEQUENCE  "CHGSHS"."SQ_PLAN_PGM_CODE"  MINVALUE 1000 
MAXVALUE 99999 INCREMENT BY 1 START
WITH 2800 CACHE 50 NOORDER  CYCLE

可以看到序列的cache分配到了2800,步长是1,cache是50,也就是说,这个序列的2750-2800这50个值已经cache到内存中了,使用序列的这50个值直接去内存拿就可以了,也代表着如果这时重启数据库,这50个值中没有使用到的部分将会丢失。

注意这里的2800是已经cache已经分配到2800,并不是当前值,下面在查询这个序列的当前值是多少。

chgshs@IVLDB> select SQ_PLAN_PGM_CODE.currval from dual;

   CURRVAL
----------
      2796

如果将序列的最小值设置大于当前值也就是2796,就会遇到上面的错误。

chgshs@IVLDB> alter sequence SQ_PLAN_PGM_CODE minvalue 2799;
alter sequence SQ_PLAN_PGM_CODE minvalue 2799
*
ERROR at line 1:
ORA-04007: MINVALUE cannot be made to exceed the current value


chgshs@IVLDB> alter sequence SQ_PLAN_PGM_CODE minvalue 2795;

Sequence altered.

像上面的需求,要将序列的最小值修改到超过当前值这种情况,就需要分成好几步进行:

首先需要修改序列的最大值,上面已经将序列的最大值从9999修改为99999了,这里就不用再次修改了。

然后需要将这个序列的当前值修改到10000或者10000之上,这是不能直接修改的,需要先修改步长,然后通过查询nextval使序列的当前值直接跳到10000或者10000之上,然后还需要再将步长修改回来。

上文查询,这个序列的当前值是2796,设置步长为7204,那么序列的下一个值就是10000。需要注意的是,这个序列的cache是50,而且使用cycle循环使用,这样设置步长就会有限制,并不能直接设置到7204。

chgshs@IVLDB> alter sequence SQ_PLAN_PGM_CODE INCREMENT BY 7204;
alter sequence SQ_PLAN_PGM_CODE INCREMENT BY 7204
*
ERROR at line 1:
ORA-04013: number to CACHE must be less than one cycle

Oracle官网给了个cache最大值的计算公式,MAX(CAHCE)=(CEIL (MAXVALUE – MINVALUE)) / ABS (INCREMENT),那么步长最大值=序列最大值减去序列最小值然后除以CACHE值也应该是成立的。虽然计算出的结果不是很准确,但也不会差太多。

NOCACHE就不受这个限制,所以在修改步长超过限制范围的情况下,指定NOCHACHE就可以避免ORA-04013错误。

chgshs@IVLDB> alter sequence SQ_PLAN_PGM_CODE INCREMENT BY 7204 nocache;

Sequence altered.

接下来还需要查询序列的nextval,使序列的当前值从2796直接跳到10000。

chgshs@IVLDB> select SQ_PLAN_PGM_CODE.currval from dual;

   CURRVAL
----------
      2796

chgshs@IVLDB> select SQ_PLAN_PGM_CODE.nextval from dual;

   NEXTVAL
----------
     10000

序列的当前值跳到要设置的10000之后,还要将步长重新设置为1,并且把cache设置回原来的50。

chgshs@IVLDB> alter sequence SQ_PLAN_PGM_CODE INCREMENT BY 1 cache 50;

Sequence altered.

然后才可以修改序列的最小值为10000。

chgshs@IVLDB> alter sequence SQ_PLAN_PGM_CODE MINVALUE 10000;

Sequence altered.

这样才将序列的最小值和最大值修改完毕,需要经历好几个步骤,如果这个序列频繁被使用的话,可能还会有问题。

最后和开发人员商量一下,在使用这个序列的相对空闲期,用下面两条命令搞定。

drop sequence SQ_PLAN_PGM_CODE;

create sequence SQ_PLAN_PGM_CODE minvalue 10000 maxvalue 99999 INCREMENT BY 1 START WITH 10000 CACHE 50 NOORDER  CYCLE;

对,就是删除重建,高效、快速、不闹心,毫秒级操作,瞬间完成,最后确认,对业务没有造成影响。

至于为什么不是只调整最大值就行呢?为什么最小值也要调整呢?开发人员的解释是,要保证使用序列生成的数据长度一致。

至于之前的数据比新生产的数据还是少一位怎么办?开发人员的解释是,之前的数据不用管,只要保证新生产的数据长度一致就行。

呃,这是什么逻辑?

最后,这个序列变更的需求又有变化,开发人员提需求,开发领导审批的时候,说为了保证之后也不会遇到这种序列导致数据重复的情况,要求把最小值和最大值都改成8位数,也就是10000000和99999999。之前那1000-9999的序列用了3年多才循环,这是暗示公司的业务要飞速发展吗?

本文固定链接: http://www.dbdream.com.cn/2018/03/oracle-%e6%9b%b4%e6%94%b9%e5%ba%8f%e5%88%97%e5%b1%9e%e6%80%a7%e9%81%87%e5%88%b0ora-04007-%e9%94%99%e8%af%af/ | 信春哥,系统稳,闭眼上线不回滚!

该日志由 dbdream 于2018年03月05日发表在 Oracle, oracle 11g 分类下, 你可以发表评论,并在保留原文地址及作者的情况下引用到你的网站或博客。
原创文章转载请注明: Oracle 更改序列属性遇到ORA-04007 错误 | 信春哥,系统稳,闭眼上线不回滚!
关键字: ,

Oracle 更改序列属性遇到ORA-04007 错误:等您坐沙发呢!

发表评论

快捷键:Ctrl+Enter