advanceSequences.sql

set serverout on
/* advance_sequences.sql
   kodner, 2008
   for every sequence in user_sequences, modify the INCREMENT BY value,
   then advance the sequence once, then restore the INCREMENT BY value
   back to one.  This script can be easily modified to increment
   just a single sequnce at a time, or to change the increment by value
*/
   
DECLARE
  v_val NUMBER;
  v_increment NUMBER := 10000;

  CURSOR c_sequence IS
    SELECT  sequence_name
      FROM  user_sequences;
BEGIN
  FOR v_sequence IN c_sequence
  LOOP
   BEGIN
    EXECUTE IMMEDIATE 'ALTER SEQUENCE '||v_sequence.sequence_name
      ||' INCREMENT BY '||v_increment;
    EXECUTE IMMEDIATE 'SELECT '||v_sequence.sequence_name||'.nextval from dual' 
      into v_val;
    EXECUTE IMMEDIATE 'ALTER SEQUENCE '||v_sequence.sequence_name
      ||' INCREMENT BY 1';
   EXCEPTION
   WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE('error with sequence '||v_sequence.sequence_name||' '||sqlerrm);
end;
  END LOOP;
END;
/