An explanation of how the Oracle reset sequence starts with a specified number

The original Oracle reset sequence begins with a specified number of methods

Resets the Oracle sequence starting with the specified number

DeclareN Number(Ten); V_startnum Number(Ten):=10000001;--starting from how muchV_step Number(Ten):=1;--Step inTsqlvarchar2( $); V_seqnamevarchar2( $):='mip_jf_sequence';--Sequence namebeginExecuteImmediate'Select'||V_seqname||'. Nextval from dual'  intoN;n:=V_startnum-N-V_step;--starting from 10000001Tsql='Alter sequence'||V_seqname||'Increment by'||N;Executeimmediate TSQL;ExecuteImmediate'Select'||V_seqname||'. Nextval from dual'  intoN;tsql:='Alter sequence'||V_seqname||'Increment by'||V_step;Executeimmediate TSQL;End;

A simple way to reset sequence values without deleting a rebuild

In general, the sequence is often used in the actual development of an object, it is very convenient to generate the primary key, but sometimes we need to reset it to zero, usually in the same way is deleted and recreated.

Sql> CreateSequence Seq_1 increment by 1Start with 1MaxValue999999999; the sequence has been created.
SQL> Create or Replace procedureSeq_reset (v_seqnamevarchar2) as2N Number(Ten);3Tsqlvarchar2( -);4 begin5 ExecuteImmediate'Select'||V_seqname||'. Nextval from dual'  intoN;6N:=-(n-1);7Tsql='Alter sequence'||V_seqname||'Increment by'||N;8 Executeimmediate TSQL;9 ExecuteImmediate'Select'||V_seqname||'. Nextval from dual'  intoN;TenTsql='Alter sequence'||V_seqname||'Increment by 1'; One Executeimmediate TSQL; A EndSeq_reset; - /The process has been created. SQL> SelectSeq_1.nextval fromdual; Nextval---------2SQL> /Nextval---------3SQL> /Nextval---------4SQL> /Nextval---------5SQL> execSeq_reset ('seq_1');P L/The SQL process has completed successfully. SQL> SelectSeq_1.currval fromdual; Currval---------1SQL> 

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 perfected, and no further narration is allowed here.

Oracle Reset sequence (do not remove rebuild mode)

In Oracle, it is common to reset the self-sequence to the initial 1 o'clock, which is removed and rebuilt, which has many drawbacks, and the functions and stored procedures that depend on it are invalidated and need to be recompiled.
But there is a kind of ingenious way, do not delete, use 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 procedureSeq_reset (v_seqnamevarchar2) asN Number(Ten); TSQLvarchar2( -);beginExecuteImmediate'Select'||V_seqname||'. Nextval from dual'  intoN;n:=-(n-1); TSQL:='Alter sequence'||V_seqname||'Increment by'||N;Executeimmediate TSQL;ExecuteImmediate'Select'||V_seqname||'. Nextval from dual'  intoN;tsql:='Alter sequence'||V_seqname||'Increment by 1';Executeimmediate TSQL;EndSeq_reset;

An explanation of how the Oracle reset sequence starts with a specified number