Detailed description of how to reset a sequence from a specified number in oracle

The code for resetting the oracle sequence starts from the specified number: declarennumber (10); v_startnumnumber (10): 10000001; -- Starting from the number of v_stepnumber (10): 1; -- stepping tsqlvarchar2 (200 ); v_seqnamevarchar2 (200): MIP_JF_SEQUENCE; -- sequence name beginexecuteimmediateselect

The code for resetting the oracle sequence starts from the specified number: declare n number (10); v_startnum number (10): = 10000001; -- v_step number (10): = 1; -- step tsql varchar2 (200); v_seqname varchar2 (200): = 'mip _ JF_SEQUENCE '; -- sequence name begin execute immediate 'select'

Resets the oracle sequence from a specified number.

The Code is as follows:
Declare
N number (10 );
V_startnum number (10): = 10000001; -- start from the number
V_step number (10): = 1; -- step
Tsql varchar2 (200 );
V_seqname varchar2 (200): = '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 | 'crement by' | n;
Execute immediate tsql;
Execute immediate 'select' | v_seqname | '. nextval from dual' into n;
Tsql: = 'alter sequence '| v_seqname | 'crement by' | v_step;
Execute immediate tsql;
End;

It is worthwhile to simply reset the sequence without deleting and recreating the sequence. Generally speaking, sequences are often used in actual development. It is very convenient to use them to generate primary keys, but sometimes we need to reset them to zero, the common method is to delete the file and recreate it.
Next, let's look at another method:

The Code is as follows:
SQL> create sequence seq_1 increment by 1 start with 1 maxvalue 999999999;
The sequence has been created.
SQL> create or replace procedure seq_reset (v_seqname varchar2)
2 n number (10 );
3 tsql varchar2 (100 );
4 begin
5 execute immediate 'select' | v_seqname | '. nextval from dual' into n;
6 n: =-(n-1 );
7 tsql: = 'alter sequence '| v_seqname | 'crement by' | n;
8 execute immediate tsql;
9 execute immediate 'select' | v_seqname | '. nextval from dual' into n;
10 tsql: = 'alter sequence '| v_seqname | 'crement by 1 ';
11 execute immediate tsql;
12 end seq_reset;
13/
The process has been 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 is successfully completed.
SQL> select seq_1.currval from dual;
CURRVAL
---------
1
SQL>

In this way, you can call this process at any time to reset the sequence.
The writing of this stored procedure is too hasty and can be further improved.
Oracle reset sequence (reconstruction mode not deleted)
In Oracle, when auto-incrementing sequence is reset to the initial 1, it is deleted and re-built. This method has many drawbacks. Functions and stored procedures dependent on it will become invalid and need to be re-compiled.
However, there is a clever way to do this without deleting it. You can use the step size parameter to first find the nextval of sequence, remember to change the increment value to the negative value (which goes in turn), and then change it back.
Assume the sequence name to be modified: seq_name

1. select seq_name.nextval from dual; // assume that the expected result is 5656.
2. alter sequence seq_name increment by-5655; // Note:-(n-1)
3. select seq_name.nextval from dual; // run the following command again and set it to 1.
4. alter sequence seq_name increment by 1; // restore

You can write a stored procedure. The following is a complete stored procedure, and then you can call the parameter passing function:

The Code is as follows:
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 | 'crement by' | n;
Execute immediate tsql;
Execute immediate 'select' | v_seqname | '. nextval from dual' into n;
Tsql: = 'alter sequence '| v_seqname | 'crement by 1 ';
Execute immediate tsql;
End seq_reset;