oracle每日重置的序列,Oracle序列批量重置

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;