Reset von Oracle Sequences

English version

Mit Bordmitteln der Oracle-Sequences ist es nicht möglich, den Wert zurückzusetzen. Da ist schon das einfachste ein DROP & CREATE, aber diese Variante löscht natürlich auch alle Berechtigungen und somit auch wenig zufriedenstellend.

Mit Hilfe der ALTER-Optionen für Sequences ist es aber durchaus möglich, den Wert zurückzusetzen ohne die Sequence neu anzulegen. Der Ablauf sieht im groben so aus:

1. Sequence auf “cycle” setzen

2. maximalen Wert auf den aktuellen setzen

3. Startwert auf den gewünschten setzen

4. einmal .NEXTVAL antriggern

5. Eigenschaften der Sequence wieder zurücksetzen.

Um den Wert einer Sequence zurück auf einen kleineren Wert zu setzen kann diese Procedure verwendet werden:

 Procedure reset_sequence
    (p_schema varchar2,
     p_seqname varchar2,
     p_newvalue number) IS
  cid INTEGER;
  sequence_reset_exception EXCEPTION;
  cursor c_wert is
    SELECT
     SEQUENCE_OWNER,
     SEQUENCE_NAME,
     MIN_VALUE,
     MAX_VALUE,
     INCREMENT_BY,
     CYCLE_FLAG,
     ORDER_FLAG,
     CACHE_SIZE,
     LAST_NUMBER
  FROM SYS.ALL_SEQUENCES
  WHERE sequence_owner = upper(p_schema)
    and sequence_name = upper(p_seqname);
  r_wert c_wert%rowtype;
  v_wert number;
  v_cycle varchar2(100);
 BEGIN
  open c_wert;
  fetch c_wert into r_wert;
  close c_wert;
  --Aktuellen Wert der Sequence laden
  cid := dbms_sql.open_cursor;
  dbms_sql.parse(cid, 'select '||p_schema||'.'||p_seqname||'.nextval VALUE from dual' , dbms_sql.v7);
  dbms_sql.define_column(cid, 1, v_wert);
  if dbms_sql.execute_and_fetch(cid)<>1 then dbms_sql.close_cursor(cid); raise sequence_reset_exception; end if;
  dbms_sql.column_value(cid, 1, v_wert);
  dbms_sql.close_cursor(cid);
  -- Sequence in einen Status setzen, indem Sie bearbeitet werden kann
  cid := dbms_sql.open_cursor;
  dbms_sql.parse(cid, 'ALTER SEQUENCE '||p_schema||'.'||p_seqname||' INCREMENT BY 1 MINVALUE '||to_char(p_newvalue - 1)||
    ' MAXVALUE '||v_wert||' CYCLE ORDER NOCACHE', dbms_sql.v7);
  if dbms_sql.execute(cid)<>0 then dbms_sql.close_cursor(cid); raise sequence_reset_exception; end if;
  dbms_sql.close_cursor(cid);
  --Sequence einen "hochzahlen", bewirkt neuanfang
  cid := dbms_sql.open_cursor;
  dbms_sql.parse(cid, 'select '||p_schema||'.'||p_seqname||'.nextval VALUE from dual' , dbms_sql.v7);
  if dbms_sql.execute_and_fetch(cid)<>1 then dbms_sql.close_cursor(cid); raise sequence_reset_exception; end if;
  dbms_sql.close_cursor(cid);
  -- Sequence in alten Status setzen
  if p_newvalue -1 < r_wert.min_value then
    r_wert.min_value := p_newvalue -1 ;
  end if;
  if r_wert.cycle_flag = 'Y' then
    v_cycle := ' CYCLE ';
  else
    v_cycle := ' NOCYCLE ';
  end if;
  if r_wert.order_flag = 'Y' then
    v_cycle := v_cycle||' ORDER ';
  else
    v_cycle := v_cycle||' NOORDER ';
  end if;
  cid := dbms_sql.open_cursor;
  dbms_sql.parse(cid, 'ALTER SEQUENCE '||p_schema||'.'||p_seqname||
                        ' INCREMENT BY '||TO_CHAR(r_wert.INCREMENT_BY) ||
                        ' MINVALUE '||to_char(r_wert.min_value)||
                        ' MAXVALUE '||to_char(r_wert.max_value)||
                        v_cycle || ' CACHE '||to_char(r_wert.cache_size), dbms_sql.v7);
  if dbms_sql.execute(cid)<>0 then dbms_sql.close_cursor(cid); raise sequence_reset_exception; end if;
  dbms_sql.close_cursor(cid);
 EXCEPTION WHEN OTHERS THEN
   dbms_sql.close_cursor(cid);
   raise;
 END;

Hinweis: die Procedure ist z. Zt. nicht dazu ausgelegt, einer Sequence einen höheren Wert zuzuweisen. Sie verwedet ausserdem ausschließlich das DBMS_SQL Package und kein EXECUTE IMMEDIATE, so dass sie auch auf etwas in die Jahre gekommenen Modellen der Oracle-Datenbanken lauffähig sein sollte.