Frage Die beste Methode, um eine Oracle-Sequenz auf den nächsten Wert in einer vorhandenen Spalte zurückzusetzen?


Aus irgendeinem Grund haben Personen in der Vergangenheit Daten ohne sequenz.NEXTVAL eingefügt. Wenn ich also sequenz.NEXTVAL benutze, um eine Tabelle zu füllen, bekomme ich eine PK-Verletzung, da diese Nummer bereits in der Tabelle verwendet wird.

Wie kann ich den nächsten Wert aktualisieren, damit er verwendbar ist? Im Moment füge ich nur immer wieder ein, bis es erfolgreich ist (INSERT INTO tbl (pk) VALUES (sequence.NEXTVAL)), und das synchronisiert den nextval.


33
2018-05-23 15:08


Ursprung


Antworten:


Sie können die Cachegröße vorübergehend erhöhen und eine Dummy-Auswahl vornehmen und dann die Cachegröße wieder auf 1 zurücksetzen

ALTER SEQUENCE mysequence INCREMENT BY 100;

select mysequence.nextval from dual;

ALTER SEQUENCE mysequence INCREMENT BY 1;

59
2018-05-23 15:09



Mit diesen beiden Verfahren kann ich die Sequenz zurücksetzen und die Sequenz basierend auf Daten in einer Tabelle zurücksetzen (Entschuldigung für die von diesem Client verwendeten Codierungskonventionen):

CREATE OR REPLACE PROCEDURE SET_SEQ_TO(p_name IN VARCHAR2, p_val IN NUMBER)
AS
   l_num   NUMBER;
BEGIN
   EXECUTE IMMEDIATE 'select ' || p_name || '.nextval from dual' INTO l_num;

   -- Added check for 0 to avoid "ORA-04002: INCREMENT must be a non-zero integer"
   IF (p_val - l_num - 1) != 0
   THEN
      EXECUTE IMMEDIATE 'alter sequence ' || p_name || ' increment by ' || (p_val - l_num - 1) || ' minvalue 0';
   END IF;

   EXECUTE IMMEDIATE 'select ' || p_name || '.nextval from dual' INTO l_num;

   EXECUTE IMMEDIATE 'alter sequence ' || p_name || ' increment by 1 ';

   DBMS_OUTPUT.put_line('Sequence ' || p_name || ' is now at ' || p_val);
END;

CREATE OR REPLACE PROCEDURE SET_SEQ_TO_DATA(seq_name IN VARCHAR2, table_name IN VARCHAR2, col_name IN VARCHAR2)
AS
   nextnum   NUMBER;
BEGIN
   EXECUTE IMMEDIATE 'SELECT MAX(' || col_name || ') + 1 AS n FROM ' || table_name INTO nextnum;

   SET_SEQ_TO(seq_name, nextnum);
END;

14
2017-07-24 21:53



Mit Orakel 10,2 g:

select  level, sequence.NEXTVAL
from  dual 
connect by level <= (select max(pk) from tbl);

setzt den aktuellen Sequenzwert auf den Maximalwert (pk) Ihrer Tabelle (d. h. der nächste Aufruf von NEXTVAL wird Ihnen das richtige Ergebnis liefern); Wenn Sie Toad verwenden, drücken Sie F5, um die Anweisung auszuführen, nicht F9, die die Ausgabe pausiert (wodurch das Inkrement nach normalerweise 500 Zeilen gestoppt wird). Gute Seite: Diese Lösung ist nur DML, nicht DDL. Nur SQL und kein PL-SQL. Schlechte Seite: Diese Lösung druckt maximale (pk) Zeilen der Ausgabe, d.h. ist normalerweise langsamer als die ALTER SEQUENCE-Lösung.


9
2017-10-25 08:25



Wenn Sie mit einer Zeitspanne rechnen können, in der sich die Tabelle in einem stabilen Zustand befindet und keine neuen Einfügungen stattfinden, sollten Sie dies tun (ungetestet):

DECLARE
  last_used  NUMBER;
  curr_seq   NUMBER;
BEGIN
  SELECT MAX(pk_val) INTO last_used FROM your_table;

  LOOP
    SELECT your_seq.NEXTVAL INTO curr_seq FROM dual;
    IF curr_seq >= last_used THEN EXIT;
    END IF;
  END LOOP;
END;

Dadurch können Sie die Sequenz wieder mit der Tabelle synchronisieren, ohne die Sequenz löschen zu müssen. Es verwendet auch keine DDL, also werden keine impliziten Commits ausgeführt. Natürlich wirst du die Leute jagen und schlagen müssen, die darauf bestehen, die Sequenz nicht zu benutzen, um die Kolumne zu füllen ...


9
2018-05-23 15:19



In meinem Fall habe ich eine Sequenz namens PS_LOG_SEQ welcher hatte a LAST_NUMBER = 3920.

Ich habe dann einige Daten aus importiert PROD zu meiner lokalen Maschine und in die PS_LOG Tabelle. Produktionsdaten hatten mehr als 20000 Zeilen mit der letzten LOG_ID (Primärschlüssel) ist 20070. Nach dem Importieren habe ich versucht, neue Zeilen in diese Tabelle einzufügen, aber beim Speichern habe ich eine Ausnahme wie diese erhalten:

ORA-00001: unique constraint (LOG.PS_LOG_PK) violated

Sicher hat das mit der Sequenz zu tun PS_LOG_SEQ in Verbindung mit PS_LOG Tabelle. Das LAST_NUMBER kollidierte mit importierten Daten, die bereits den nächsten ID - Wert aus der PS_LOG_SEQ.

Um das zu lösen, habe ich diesen Befehl verwendet, um die Sequenz auf den neuesten Stand zu bringen. max(LOG_ID) + 1:

alter sequence PS_LOG_SEQ restart start with 20071;

Dieser Befehl setzt den Befehl zurück LAST_NUMBER Wert und ich könnte dann neue Zeilen in die Tabelle einfügen. Keine Kollision mehr. :)

Hinweis: Dies alter sequence Befehl ist neu in Oracle 12c.


5
2018-06-19 23:26