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、非重建方式，参考：非重建的链接