Oracle 重置序列

Oracle将自增sequence重置为初始值1时，若是删除再重建，那么依赖它的函数和存储过程将失效，需要重新编译。

这里利用相反的过程，利用负值步长参数，将递增值改为(sequence.NEXTVAL-1)，然后再改回来。

 假设需要修改的序列名：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;

 

转载于:https://www.cnblogs.com/sallet/p/4200454.html