Oracle Reset sequence (do not remove rebuild mode)

Sequences in Oracle are typically used to generate serial numbers, so we need to reset them (such as resetting them every morning), although we can reset the sequence by recompiling, but this approach has drawbacks such as invalidating the stored procedure or function associated with the sequence, and so on, which requires recompilation , so you need a way to not compile or reset the sequence--

This way, do not delete, using the step parameter, first find out the sequence nextval, remember, the increment to negative this value (in turn), and then change back.

Suppose you need to modify the sequence name: Seq_name

1, select Seq_name.nextval from dual; Assuming the results are 5656

2, alter sequence seq_name increment by-5655; Note Yes-(n-1)

3, select Seq_name.nextval from dual;//again, walk, reset to 1

4, alter sequence seq_name increment by 1;//restore

You can write a stored procedure, the following is the complete stored procedure, and then call the parameters:

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;

Transferred from: http://www.cnblogs.com/huangzhen/archive/2011/09/19/2181257.html

Oracle Reset sequence (do not remove rebuild mode)