An explanation of how Oracle reset sequences start with a specified number

Reset Oracle sequence starting from a specified number

The code is as follows:
Declare
N Number (10);
V_startnum Number (10): =10000001;--from how many start
V_step Number (10): =1;--Step
TSQL VARCHAR2 (200);
V_seqname varchar2: = ' mip_jf_sequence ';--sequence name
Begin
Execute immediate ' SELECT ' | | v_seqname| | '. Nextval from dual ' into N;
n:=v_startnum-n-v_step;--starting from 10000001
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 ' | | V_step;
Execute immediate TSQL;
End

It is worth the easy way to reset a sequence without deleting the Rebuild method. In general, the sequence in the actual development process is often used in an object, it is very convenient to generate a primary key, but sometimes we need to reset it to zero, usually in the way is deleted and recreated.
Here's another way to look at this:

The

code is as follows:
sql> create sequence seq_1 increment by 1-start with 1 maxvalue 999999999;
Sequence has been created.
sql> Create or replace procedure Seq_reset (V_seqname varchar2) as
2 N number (ten);
3 tsql varchar2 (MB);
4 begin
5 Execute immediate ' SELECT ' | | v_seqname| | '. Nextval from dual ' into N;
6 n:=-(n-1);
7 tsql:= ' alter sequence ' | | v_seqname| | ' increment by ' | | N
8 execute immediate TSQL;
9 Execute immediate ' SELECT ' | | v_seqname| | '. Nextval from dual ' into N;
tsql:= ' alter sequence ' | | v_seqname| | ' increment by 1 ';
Execute immediate TSQL;
End Seq_reset; The
/
procedure was created.
Sql> Select Seq_1.nextval from dual;
Nextval
---------
2
sql>/
Nextval
---------
3
sql>/
Nextval
---------
4
sql>/
Nextval
---------
5
sql> exec seq_reset (' seq_1 ');
The PL/SQL process has completed successfully.
Sql> Select Seq_1.currval from dual;
Currval
---------
1
Sql>

This can be done by calling this procedure at any time to achieve the purpose of the sequence reset.
This stored procedure is written in a hurry, and can be further refined, and no longer be described here
Oracle Reset sequence (rebuild not removed)
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:

The

Code is as follows:
Create or replace procedure Seq_reset (V_seqname varchar2) as n number;
tsql varchar2 (MB);
begin< br> 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;