Oracle重置序列发生器(非重建)

前端之家收集整理的这篇文章主要介绍了Oracle重置序列发生器(非重建)前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

1、背景

1.1 背景

项目中有5个序列发生器(分别为:seq1、seq2、seq3、seq4、seq5),各序列发生的作用是产生[10000,19999)、[20000,29999)、[30000,39999)、[40000,49999)、[50000,59999)的数值。创建序列的DDL如下:

CREATE SEQUENCE seq1
  START WITH 10001
  MAXVALUE 999999999999999999999999999
  MINVALUE 10001
  increment by 1
  NOCYCLE
  NOCACHE
  ORDER;

其他的同样,区别只是开始值不同

1.2 问题

目前每天晚上00:00有定时任务,作用是删掉序列发生器,然后重建,保证每天的序列发生器是从起始值开始的。

最近一段时间偶尔出问题,经查询,重置序列发生器的定时任务在运行时,有其他定时任务正在使用这些序列发生器,从而导致定时任务失败。

因此,现场要求:既要重置序列发生器,又避免发生异常。

所以,采用非重建的方式重置序列发生器

2、解决方

创建procedure,如下:

/*
  创建人:郑林 2017-8-30
  方法说明:重置序列发生器
  参数说明:v_seqname序列发生器名称;v_standard序列的起始号码
*/
create or replace procedure seq_reset(v_seqname varchar2,v_standard number) as
  n    number(10);
  tsql varchar2(100);
  begin
       --获取序列的当前值
       execute immediate 'select '|| v_seqname ||'.nextval from dual' into n;
       n :=v_standard-(n-1);
       --更改步长
       tsql := 'alter sequence '|| v_seqname ||' increment by ' || n;
       execute immediate tsql;
       
       --获取当前值
       begin
               execute immediate 'select '|| v_seqname ||'.nextval from dual' into n;
       exception                
               when others then
                 if sqlcode='-8004' then
                    tsql:='alter sequence '||v_seqname||' increment by 1';
                    execute immediate tsql;
                 end if;
       end; 
       
       --恢复步长
       tsql := 'alter sequence ' || v_seqname ||' increment by 1';
       execute immediate tsql;
  
end seq_reset;

参数中v_standard为起始值。

这样,重写定时任务,便可解决当前问题

exec seq_reset('seq1',1000);

3、参考资料

1、动态sql,参考:动态SQL资料链接

2、非重建方式,参考:非重建的链接

猜你在找的Oracle相关文章