Oracle Reset sequence (rebuild not removed) ____oracle

Oracle typically resets the sequence to initial 1 o'clock, which is deleted and rebuilt, which has many drawbacks, and the functions and stored procedures that depend on it will fail and need to be recompiled.

But there is a clever way, do not delete, using the step parameters, first detect the sequence of the Nextval, remember, the increment to negative this value (the reverse), and then change back.

Assume the name of the sequence you want to modify: Seq_name

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

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

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

4. Alter sequence seq_name increment by 1;//restore

You can write a stored procedure, and the following is a complete stored procedure, and then invoke the argument:

Create or Replace procedure Seq_reset (V_seqname varchar2) as n number (a);
TSQL VARCHAR2 (m);
 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;