Differences

This shows you the differences between two versions of the page.

Link to this comparison view

blog:adjustingsequences [2009/11/27 17:53] (current)
Line 1: Line 1:
 +====== 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).
 +
 +<code oracle8>
 +  drop sequence xxx;
 +  create sequence xxx starting with 1;
 +</​code>​
 +
 +A better approach is to use Dynamic SQL to adjust alter the sequence in-situ to the desired value, thus avoiding the above problem.
 +
 +<code oracle8>
 +/** 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;
 +</​code>​
 +
 +====== Reset all user sequences ======
 +The above function can then be neatly wrapped in a loop to reset all the sequences number for a user.
 +<code oracle8>
 +/**
 +* 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;
 +</​code>​
 +
 +
 +
 +====== 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.
 +<code oracle8>
 +/**
 +  * 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;
 +</​code>​
 +{{tag>​oracle plsql programming}}