Oracle 重設序列

標籤：

Oracle將自增sequence重設為初始值1時，若是刪除再重建，那麼依賴它的函數和預存程序將失效，需要重新編譯。這裡利用相反的過程，利用負值步長參數，將遞增值改為(sequence.NEXTVAL-1)，然後再改回來。 假設需要修改的序列名：seq_name1、select seq_name.nextval from dual; //假設得到結果56562、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;

 

Oracle 重設序列