Resetting a Sequence
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;
Reset all user sequences
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;
Synchronizing sequences across a schema
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;