One of the problems with resetting an Oracle sequence number is that if you opt for the obvious drop and re-create method is that you will invalid all the triggers that use this sequence, requiring you to recompile the entire schema (or at least those invalidated triggers).

  DROP SEQUENCE xxx;
  CREATE SEQUENCE xxx starting WITH 1;

A better approach is to use Dynamic SQL to adjust alter the sequence in-situ to the desired value, thus avoiding the above problem.

/** Reset sequence for a user to a starting value.
*/
 
PROCEDURE reset_sequence (sequencename IN VARCHAR2, startvalue IN INTEGER) IS
   lsection                VARCHAR2 (256) := 'NotSet';
   curr_val   INTEGER;
   STRING     VARCHAR2 (25);
BEGIN
   lSection := 'alter sequence ' || sequencename || ' MINVALUE 0';
   EXECUTE IMMEDIATE lsection;
 
   lSection := 'SELECT ' || sequencename || '.nextval FROM dual';
   EXECUTE IMMEDIATE lSection INTO curr_val;
 
   curr_val := curr_val - startvalue + 1;
   IF curr_val < 0 THEN
      STRING := ' increment by ';
      curr_val := ABS (curr_val);
   ELSIF curr_val = 0 THEN
      RETURN;
   ELSE
      STRING := ' increment by -';
   END IF;
 
   lSection := 'alter sequence ' || sequencename || STRING || curr_val;
   EXECUTE IMMEDIATE lsection;
 
   lSection := 'SELECT ' || sequencename || '.nextval FROM dual';
   EXECUTE IMMEDIATE lSection INTO curr_val;
 
   lSection := 'alter sequence ' || sequencename || ' increment by 1';
   EXECUTE IMMEDIATE lsection;
END;

The above function can then be neatly wrapped in a loop to reset all the sequences number for a user.

/**
* Reset all sequences back to a starting default value.
*/
PROCEDURE reset_all_sequences (startvalue IN INTEGER := 1000000) IS
BEGIN
   FOR rec IN (SELECT sequence_name
                 FROM user_sequences)
   LOOP
      reset_sequence (rec.sequence_name, startvalue);
   END LOOP;
END;

Occasionally it is necessary to make the sequence numbers in one schema match those of another. This might be necessary if you have multiple testing environments which copy data from some master test user.

Using our previously defined functions we can add the following function to our arsenal.

/**
  * Synchronized the current values of all schema sequences with those from another schema.
  */
PROCEDURE clone_sequence (fromuser VARCHAR2, seq_name VARCHAR2 := '%') IS
   lsection                VARCHAR2 (356) := 'NotSet';
   seq_curvalue            NUMBER;
BEGIN
   FOR rec IN (SELECT sequence_name, last_number, increment_by
                 FROM all_sequences
                WHERE sequence_owner = fromuser
                          AND sequence_name LIKE seq_name)
   LOOP
      lsection := 'Get Current Value Number';
      seq_curvalue := rec.last_number;
 
      lsection := 'Reset sequence';
      reset_sequence(rec.sequence_name, seq_curvalue);
   END LOOP;
END;