[sql] Wie setze ich eine Sequenz in Oracle zurück?



Answers

Ein echter Neustart ist AFAIK nicht möglich. (Bitte korrigiere mich wenn ich falsch liege!).

Wenn Sie es jedoch auf 0 setzen möchten, können Sie es einfach löschen und neu erstellen.

Wenn Sie einen bestimmten Wert festlegen möchten, können Sie INCREMENT auf einen negativen Wert setzen und den nächsten Wert abrufen.

Das heißt, wenn Ihre Sequenz bei 500 ist, können Sie sie über 100 auf 100 setzen

ALTER SEQUENCE serial INCREMENT BY -400;
SELECT serial.NEXTVAL FROM dual;
ALTER SEQUENCE serial INCREMENT BY 1;
Question

In PostgreSQL kann ich so etwas machen:

ALTER SEQUENCE serial RESTART WITH 0;

Gibt es ein Oracle-Äquivalent?




Es gibt eine andere Möglichkeit, eine Sequenz in Oracle zurückzusetzen: maxvalue Eigenschaften maxvalue und cycle . Wenn das nextval der Sequenz den maxvalue , wenn die cycle Eigenschaft gesetzt ist, es erneut vom minvalue der Sequenz beginnen.

Der Vorteil dieser Methode im Vergleich zur Einstellung eines negativen increment by ist, dass die Sequenz weiterhin verwendet werden kann, während der Reset-Prozess ausgeführt wird, wodurch die Wahrscheinlichkeit verringert wird, dass Sie eine Ausfallsituation für das Zurücksetzen benötigen.

Der Wert für maxvalue muss größer sein als der aktuelle nextval die folgende Prozedur einen optionalen Parameter, der einen Puffer für den Fall ermöglicht, dass zwischen dem Auswählen des nextval in der Prozedur und dem Festlegen der cycle nextval erneut auf die Sequenz zugegriffen wird.

create sequence s start with 1 increment by 1;

select s.nextval from dual
connect by level <= 20;

   NEXTVAL
----------
         1 
...
        20

create or replace procedure reset_sequence ( i_buffer in pls_integer default 0)
as
  maxval pls_integer;
begin

  maxval := s.nextval + greatest(i_buffer, 0); --ensure we don't go backwards!
  execute immediate 'alter sequence s cycle minvalue 0 maxvalue ' || maxval;
  maxval := s.nextval;
  execute immediate 'alter sequence s nocycle maxvalue 99999999999999';

end;
/
show errors

exec reset_sequence;

select s.nextval from dual;

   NEXTVAL
----------
         1 

Das Verfahren, wie es stillsteht, ermöglicht die Möglichkeit, dass eine andere Sitzung den Wert 0 abruft, was für Sie ein Problem sein kann oder nicht. Wenn ja, könnte man immer:

  • Setzen Sie minvalue 1 in der ersten Änderung
  • Schließen Sie den zweiten nextval Abruf aus
  • Verschieben Sie die Anweisung, um die nocycle in eine andere Prozedur zu setzen, die zu einem späteren Zeitpunkt ausgeführt wird (vorausgesetzt, Sie möchten dies tun).



Das folgende Skript legte die Sequenz auf einen gewünschten Wert fest:

Gegeben eine neu angelegte Sequenz namens PCS_PROJ_KEY_SEQ und Tabelle PCS_PROJ:

BEGIN
   DECLARE
      PROJ_KEY_MAX       NUMBER := 0;
      PROJ_KEY_CURRVAL   NUMBER := 0;
   BEGIN

    SELECT MAX (PROJ_KEY) INTO PROJ_KEY_MAX FROM PCS_PROJ;
    EXECUTE IMMEDIATE 'ALTER SEQUENCE PCS_PROJ_KEY_SEQ INCREMENT BY ' || PROJ_KEY_MAX;
    SELECT PCS_PROJ_KEY_SEQ.NEXTVAL INTO PROJ_KEY_CURRVAL FROM DUAL;
    EXECUTE IMMEDIATE 'ALTER SEQUENCE PCS_PROJ_KEY_SEQ INCREMENT BY 1';

END;
END;
/



Ich mache eine Alternative, dass der Benutzer die Werte nicht kennen muss, das System erhält und Variablen zum Aktualisieren verwendet.

--Atualizando sequence da tabela SIGA_TRANSACAO, pois está desatualizada
DECLARE
 actual_sequence_number INTEGER;
 max_number_from_table INTEGER;
 difference INTEGER;
BEGIN
 SELECT [nome_da_sequence].nextval INTO actual_sequence_number FROM DUAL;
 SELECT MAX([nome_da_coluna]) INTO max_number_from_table FROM [nome_da_tabela];
 SELECT (max_number_from_table-actual_sequence_number) INTO difference FROM DUAL;
IF difference > 0 then
 EXECUTE IMMEDIATE CONCAT('alter sequence [nome_da_sequence] increment by ', difference);
 --aqui ele puxa o próximo valor usando o incremento necessário
 SELECT [nome_da_sequence].nextval INTO actual_sequence_number from dual;
--aqui volta o incremento para 1, para que futuras inserções funcionem normalmente
 EXECUTE IMMEDIATE 'ALTER SEQUENCE [nome_da_sequence] INCREMENT by 1';
 DBMS_OUTPUT.put_line ('A sequence [nome_da_sequence] foi atualizada.');
ELSE
 DBMS_OUTPUT.put_line ('A sequence [nome_da_sequence] NÃO foi atualizada, já estava OK!');
END IF;
END;



Hier ist ein robusteres Verfahren zum Ändern des nächsten Werts, der von einer Sequenz zurückgegeben wird, und noch viel mehr.

  • Zunächst einmal schützt es vor SQL-Injection-Angriffen, da keine der übergebenen Strings verwendet werden, um direkt eine der dynamischen SQL-Anweisungen zu erstellen,
  • Zweitens verhindert es, dass der nächste Sequenzwert außerhalb der Grenzen der minimalen oder maximalen Sequenzwerte gesetzt wird. Der next_value ist! = min_value und zwischen min_value und max_value .
  • Drittens berücksichtigt es die aktuelle (oder vorgeschlagene) increment_by Einstellung sowie alle anderen Sequenzeinstellungen beim Bereinigen.
  • Viertens sind alle Parameter außer dem ersten optional und nehmen, wenn sie nicht angegeben sind, die aktuelle Sequenzeinstellung als Standard an. Wenn keine optionalen Parameter angegeben sind, wird keine Aktion ausgeführt.
  • Wenn Sie schließlich versuchen, eine Sequenz zu ändern, die nicht existiert (oder nicht dem aktuellen Benutzer gehört), wird ein ORA-01403: no data found Fehler ORA-01403: no data found .

Hier ist der Code:

CREATE OR REPLACE PROCEDURE alter_sequence(
    seq_name      user_sequences.sequence_name%TYPE
  , next_value    user_sequences.last_number%TYPE := null
  , increment_by  user_sequences.increment_by%TYPE := null
  , min_value     user_sequences.min_value%TYPE := null
  , max_value     user_sequences.max_value%TYPE := null
  , cycle_flag    user_sequences.cycle_flag%TYPE := null
  , cache_size    user_sequences.cache_size%TYPE := null
  , order_flag    user_sequences.order_flag%TYPE := null)
  AUTHID CURRENT_USER
AS
  l_seq user_sequences%rowtype;
  l_old_cache user_sequences.cache_size%TYPE;
  l_next user_sequences.min_value%TYPE;
BEGIN
  -- Get current sequence settings as defaults
  SELECT * INTO l_seq FROM user_sequences WHERE sequence_name = seq_name;

  -- Update target settings
  l_old_cache := l_seq.cache_size;
  l_seq.increment_by := nvl(increment_by, l_seq.increment_by);
  l_seq.min_value    := nvl(min_value, l_seq.min_value);
  l_seq.max_value    := nvl(max_value, l_seq.max_value);
  l_seq.cycle_flag   := nvl(cycle_flag, l_seq.cycle_flag);
  l_seq.cache_size   := nvl(cache_size, l_seq.cache_size);
  l_seq.order_flag   := nvl(order_flag, l_seq.order_flag);

  IF next_value is NOT NULL THEN
    -- Determine next value without exceeding limits
    l_next := LEAST(GREATEST(next_value, l_seq.min_value+1),l_seq.max_value);

    -- Grab the actual latest seq number
    EXECUTE IMMEDIATE
        'ALTER SEQUENCE '||l_seq.sequence_name
            || ' INCREMENT BY 1'
            || ' MINVALUE '||least(l_seq.min_value,l_seq.last_number-l_old_cache)
            || ' MAXVALUE '||greatest(l_seq.max_value,l_seq.last_number)
            || ' NOCACHE'
            || ' ORDER';
    EXECUTE IMMEDIATE 
      'SELECT '||l_seq.sequence_name||'.NEXTVAL FROM DUAL'
    INTO l_seq.last_number;

    l_next := l_next-l_seq.last_number-1;

    -- Reset the sequence number
    IF l_next <> 0 THEN
      EXECUTE IMMEDIATE 
        'ALTER SEQUENCE '||l_seq.sequence_name
            || ' INCREMENT BY '||l_next
            || ' MINVALUE '||least(l_seq.min_value,l_seq.last_number)
            || ' MAXVALUE '||greatest(l_seq.max_value,l_seq.last_number)
            || ' NOCACHE'
            || ' ORDER';
      EXECUTE IMMEDIATE 
        'SELECT '||l_seq.sequence_name||'.NEXTVAL FROM DUAL'
      INTO l_next;
    END IF;
  END IF;

  -- Prepare Sequence for next use.
  IF COALESCE( cycle_flag
             , next_value
             , increment_by
             , min_value
             , max_value
             , cache_size
             , order_flag) IS NOT NULL
  THEN
    EXECUTE IMMEDIATE 
      'ALTER SEQUENCE '||l_seq.sequence_name
          || ' INCREMENT BY '||l_seq.increment_by
          || ' MINVALUE '||l_seq.min_value
          || ' MAXVALUE '||l_seq.max_value
          || CASE l_seq.cycle_flag
             WHEN 'Y' THEN ' CYCLE' ELSE ' NOCYCLE' END
          || CASE l_seq.cache_size
             WHEN 0 THEN ' NOCACHE'
             ELSE ' CACHE '||l_seq.cache_size END
          || CASE l_seq.order_flag
             WHEN 'Y' THEN ' ORDER' ELSE ' NOORDER' END;
  END IF;
END;



Meine Herangehensweise ist eine kleine Erweiterung von Dougmans Beispiel .

Erweiterungen sind ...

Übergeben Sie den Startwert als Parameter. Warum? Ich rufe die Sache gerne zurück und setze die Sequenz auf die maximale ID zurück, die in einer Tabelle verwendet wird . Am Ende rufe ich diesen proc von einem anderen Skript auf, das mehrere Aufrufe für eine ganze Reihe von Sequenzen ausführt, wobei nextval auf eine Ebene zurückgesetzt wird, die hoch genug ist, um keine Primärschlüsselverletzungen zu verursachen, wo ich den Wert der Sequenz für eine eindeutige Kennung verwende.

Es honoriert auch den vorherigen minvalue . Es kann tatsächlich den nächsten Wert immer höher schieben, wenn der gewünschte p_val oder existierender minvalue höher als der aktuelle oder berechnete nächste Wert ist.

Das Beste von allem, es kann aufgerufen werden, um auf einen bestimmten Wert zurückzusetzen, und warten Sie einfach, bis Sie den Wrapper sehen "Fix alle meine Sequenzen" -Prozedur am Ende.

create or replace
procedure Reset_Sequence( p_seq_name in varchar2, p_val in number default 0)
is
  l_current number := 0;
  l_difference number := 0;
  l_minvalue user_sequences.min_value%type := 0;

begin

  select min_value
  into l_minvalue
  from user_sequences
  where sequence_name = p_seq_name;

  execute immediate
  'select ' || p_seq_name || '.nextval from dual' INTO l_current;

  if p_Val < l_minvalue then
    l_difference := l_minvalue - l_current;
  else
    l_difference := p_Val - l_current;
  end if;

  if l_difference = 0 then
    return;
  end if;

  execute immediate
    'alter sequence ' || p_seq_name || ' increment by ' || l_difference || 
       ' minvalue ' || l_minvalue;

  execute immediate
    'select ' || p_seq_name || '.nextval from dual' INTO l_difference;

  execute immediate
    'alter sequence ' || p_seq_name || ' increment by 1 minvalue ' || l_minvalue;
end Reset_Sequence;

Diese Prozedur ist für sich alleine nützlich, aber jetzt fügen wir eine weitere hinzu, die sie aufruft und alles programmatisch mit einer Sequenzbenennungskonvention spezifiziert und nach dem maximalen Wert sucht, der in einer existierenden Tabelle / Feld verwendet wird ...

create or replace
procedure Reset_Sequence_to_Data(
  p_TableName varchar2,
  p_FieldName varchar2
)
is
  l_MaxUsed NUMBER;
BEGIN

  execute immediate
    'select coalesce(max(' || p_FieldName || '),0) from '|| p_TableName into l_MaxUsed;

  Reset_Sequence( p_TableName || '_' || p_Fieldname || '_SEQ', l_MaxUsed );

END Reset_Sequence_to_Data;

Jetzt kochen wir mit Gas!

Die obige Prozedur prüft den Maximalwert eines Feldes in einer Tabelle, erstellt einen Sequenznamen aus dem Tabelle / Feld-Paar und ruft "Reset_Sequence" mit diesem erfaßten Maximalwert auf.

Das letzte Stück in diesem Puzzle und das i-Tüpfelchen kommt als nächstes ...

create or replace
procedure Reset_All_Sequences
is
BEGIN

  Reset_Sequence_to_Data( 'ACTIVITYLOG', 'LOGID' );
  Reset_Sequence_to_Data( 'JOBSTATE', 'JOBID' );
  Reset_Sequence_to_Data( 'BATCH', 'BATCHID' );

END Reset_All_Sequences;

In meiner tatsächlichen Datenbank gibt es etwa hundert andere Sequenzen, die durch diesen Mechanismus zurückgesetzt werden, so dass es 97 weitere Aufrufe von Reset_Sequence_to_Data in dieser Prozedur oben gibt.

Liebe es? Hasse es? Gleichgültig?




Jezus, all diese Programmierung für nur einen Index-Neustart ... Vielleicht bin ich ein Idiot, aber für Pre-Oracle 12 (die eine Neustart-Funktion hat), was ist falsch mit einem Simpel:

drop sequence blah;
create sequence blah 

?




1) Angenommen, Sie erstellen eine SEQUENZ wie unten gezeigt:

CREATE SEQUENCE TESTSEQ
INCREMENT BY 1
MINVALUE 1
MAXVALUE 500
NOCACHE
NOCYCLE
NOORDER

2) Jetzt holen Sie Werte von SEQUENCE. Sagen wir, ich habe viermal geholt wie unten gezeigt.

SELECT TESTSEQ.NEXTVAL FROM dual
SELECT TESTSEQ.NEXTVAL FROM dual
SELECT TESTSEQ.NEXTVAL FROM dual
SELECT TESTSEQ.NEXTVAL FROM dual

3) Nach dem Ausführen der obigen vier Befehle wird der Wert der SEQUENCE 4 sein. Nun nehme ich an, dass ich den Wert der SEQUENCE auf 1 zurückgesetzt habe. Folgen Sie den folgenden Schritten. Befolgen Sie alle Schritte in der folgenden Reihenfolge:

  1. ALTER SEQUENCE TESTSEQ INCREMENT BY -3;
  2. SELECT TESTSEQ.NEXTVAL FROM dual
  3. ALTER SEQUENCE TESTSEQ INCREMENT BY 1;
  4. SELECT TESTSEQ.NEXTVAL FROM dual



Links