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;