Oracle reset sequence (reconstruction mode not deleted)

In Oracle, when auto-incrementing sequence is reset to initial 1, it is deleted and rebuilt. 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 that the sequence name to be modified is seq_name 1, select seq_name.nextval from dual; // assume that the result is 5656 2. alter sequence seq_name increment by-5655; // note that it is-(n-1) 3. select seq_name.nextval from dual; // run the following command again to reset it to 1. 4. alter sequence seq_name increment by 1. // you can write a stored procedure to restore it, the following is a complete stored PROCEDURE, and then you can call the passing parameter: 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;