Oracle序列批量重置,有两种方法。方法一,批量删除重建。方法二,修改序列increment 属性,为-(当间值-1),查询序列.nextval,还原increment 属性(ps:方法二没有测试通过,只提供思路)。
两种方法都需要注意,需要显示声明序列的删除新建权限。
create or replace procedure seq_set_zero(resultes out varchar2) is
cursor cur_seq is
select t.sequence_name,t.min_value,t.increment_by,t.last_number
from user_sequences t;
v_sequence_name varchar2(50);
v_min_value varchar2(50);
v_increment_by varchar2(50);
v_last_number varchar2(50); v_next number;
begin
/*显示声明权限*/
execute immediate 'grant create sequence to username'; --用户名
execute immediate 'grant drop any sequence to username'; --用户名 open cur_seq;
fetch cur_seq into v_sequence_name,v_min_value,v_increment_by,v_last_number;
while cur_seq %found loop
/*删除序列:*/
execute immediate 'drop sequence '||v_sequence_name;
/*创建序列:*/
execute immediate 'create sequence '||v_sequence_name||' minvalue 1 start with 1 increment by ''||v_increment_by; fetch cur_seq into v_sequence_name,v_min_value,v_increment_by,v_last_number;
end loop;
/*方法二*/
--open cur_seq;
--fetch cur_seq into v_sequence_name,v_min_value,v_increment_by,v_last_number;
--while cur_seq %found loop
--获取序列
--execute immediate 'select '||v_sequence_name||'.nextval from dual ' into v_next;
--计算需要重置的数据
--v_next := -(v_next-1);
--修改序列increment
--execute immediate 'alter sequence '||v_sequence_name||' increment by '||v_next ;
--查询序列
--execute immediate 'select '||v_sequence_name||'.nextval from dual ';
--修改序列increment
--execute immediate 'alter sequence '||v_sequence_name||' increment by '||v_increment_by;
--fetch cur_seq into v_sequence_name,v_min_value,v_increment_by,v_last_number;
--end loop;
close cur_seq; resultes := '操作成功' ; commit; exception when others then rollback; resultes := '操作失败'; end seq_set_zero;