The serial number recorded in the table is usually done by the method of sequence. Sometimes it needs to be reset in the creation project, such as in the creation stage, test stage, etc. we can reset the sequence in various ways, delete the reconstruction, sequence reset, etc. The following is an example of a stored procedure for two methods.
To implement the following stored procedure, you need to grant permissions.
GRANT CREATE SEQUENCE TO user;

1. Delete rebuild stored procedure
In fact, it can also be used for creation, because stored procedures are judged first, deleted when they exist, and then rebuilt.
The procedure flow is as follows:
Judge whether the given sequence exists or not, and delete it if it exists;
Create according to the sequence name, starting from 1 to 99999999 by default, each step is 1;

/***
  p_SetSequence Delete and rebuild the specified sequence
***/
CREATE OR REPLACE PROCEDURE p_SetSequence
(
  uSequence IN VARCHAR2     -- Specified sequence
)
AS
uNum NUMBER; uSQL VARCHAR(100);
BEGIN
  uSQL := 'DROP SEQUENCE ' || uSequence;
  SELECT COUNT(*) INTO uNum
  FROM DUAL
  WHERE EXISTS
    ( SELECT * FROM User_Sequences WHERE SEQUENCE_NAME = UPPER(uSequence) );
  IF (uNum = 1) THEN
    EXECUTE IMMEDIATE uSQL;
    DBMS_OUTPUT.PUT_LINE('sequence '|| uSequence ||' Was deleted.');
  ELSE
    DBMS_OUTPUT.PUT_LINE('sequence '|| uSequence ||' non-existent.');
  End IF;
  uSQL := 'CREATE SEQUENCE ' || uSequence;
  uSQL := uSQL ||' INCREMENT BY 1 START with 1 MAXVALUE 9999999999';
  EXECUTE IMMEDIATE uSQL;
END p_SetSequence;
/

2. Deleting a sequence will cause the stored procedures and functions related to the sequence to fail and recompile. Sometimes, in order to prevent recompilation, it cannot be deleted. So we need a method that can reset the sequence without compiling. We can reset the sequence by changing the reverse step, which is intended to return to 1, and then change to the forward step.

/***
  p_ReSetSequence Reset specified sequence
***/
create or replace procedure p_ReSetSequence
(
  uSequence IN VARCHAR2     -- Specified sequence
)
AS
uNum NUMBER(10);
uSQL VARCHAR2(100);
BEGIN
  SELECT COUNT(*) INTO uNum
  FROM DUAL
  WHERE EXISTS
    ( SELECT * FROM User_Sequences WHERE SEQUENCE_NAME = UPPER(uSequence) );
  IF (uNum = 1) THEN
    -- 1.Get the current value of the sequence
    uSQL := 'SELECT '|| uSequence ||'.NEXTVAL FROM DUAL';
    EXECUTE IMMEDIATE uSQL INTO uNum;

    -- 2.Changes the step size and direction of the sequence, changing the direction by a negative value
    uNum := - (uNum-1);
    uSQL := 'ALTER SEQUENCE '|| uSequence ||' INCREMENT BY '|| uNum;
    EXECUTE IMMEDIATE uSQL;

    -- 3.Obtain the next value of the sequence to achieve the reverse value once
    uSQL := 'SELECT '|| uSequence ||'.NEXTVAL FROM DUAL';
    EXECUTE IMMEDIATE uSQL INTO uNum;

    -- 4.Step and direction of recovery sequence
    uSQL := 'ALTER SEQUENCE '|| uSequence ||' INCREMENT BY 1';
    EXECUTE IMMEDIATE uSQL;
    DBMS_OUTPUT.PUT_LINE('sequence '|| uSequence ||' Has been reset.');
  ELSE
    DBMS_OUTPUT.PUT_LINE('sequence '|| uSequence ||' non-existent.');
  End IF;
END p_ReSetSequence;
/