Oracle中一般将自增sequence重置为初始1时,都是删除再重建,不过还有种巧妙的方式,不用删除,利用步长参数,先查出sequence的nextvalue,再把递增改为负的这个值(反过来走),然后再改回来。
假设需要修改的序列名:seq_name
1、select seq_name.nextval from dual;//假设得到结果5656
2、alter sequence seq_name increment by -5655 ;//注意是-(n-1)
3、select seq_name.nextval from dual ;//再查一遍,走一下,重置为1了
4、alter sequence seq_name increment by 1;//还原
可以官吏个存储过程,以下是完整的存储过程,然后调用传参即可。
create or replace procedure seq_reset(v_seqname varchar2) as n number(10);
tsql varchar2(100);
begin
execute immediate 'select '||v_seqname||'.nextval from dual' into n;
n:=-(n-1);
tsql:='alter sequence '||v_seqname||' increment by '|| n;
execute immediate tsql;
execute immediate 'select '||v_seqname||'.nextval from dual' into n;
tsql:='alter sequence '||v_seqname||' increment by 1';
execute immediate tsql;
end seq_reset;