Najlepszy sposób na zresetowanie sekwencji Oracle do następnej wartości w istniejącej kolumnie?

Z jakiegoś powodu, ludzie w przeszłości wstawiali dane bez użycia sekwencji.NEXTVAL. Więc kiedy idę użyć sekwencji.NEXTVAL w celu wypełnienia tabeli, dostaję PK naruszenie, ponieważ liczba ta jest już w użyciu w tabeli.

Jak mogę zaktualizować następną wartość, aby była użyteczna? W tej chwili po prostu wstawiam w kółko, aż się uda (INSERT INTO tbl (pk) VALUES (sequence.NEXTVAL)), a to synchronizuje następny.

Author: Leniel Maccaferri, 2011-05-23
Source

5 answers

Możesz tymczasowo zwiększyć rozmiar pamięci podręcznej i wybrać jeden atrapa, a następnie zresetować rozmiar pamięci podręcznej z powrotem do 1. Więc na przykład 

ALTER SEQUENCE mysequence INCREMENT BY 100;

select mysequence.nextval from dual;

ALTER SEQUENCE mysequence INCREMENT BY 1;
 60
Author: Basanth Roy,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-04-11 14:15:49

Te dwie procedury pozwalają mi zresetować sekwencję i zresetować sekwencję na podstawie danych w tabeli (przepraszamy za konwencje kodowania używane przez tego klienta): 

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
Author: Cade Roux,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-07-24 21:53:58

Z oracle 10.2 g: 

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

Ustawi bieżącą wartość sekwencji na max (pk) Twojej tabeli (tzn. następne wywołanie NEXTVAL da właściwy wynik); jeśli używasz Toad, naciśnij F5, aby uruchomić instrukcję, a nie F9, która wyświetla dane wyjściowe (zatrzymując w ten sposób przyrost po, Zwykle, 500 wierszach). Dobra Strona: tym rozwiązaniem jest tylko DML, a nie DDL. Tylko SQL i bez PL-SQL. Bad side : to rozwiązanie wypisuje maks. (pk) wiersze danych wyjściowych, tzn. jest zwykle wolniejsze niż rozwiązanie ALTER SEQUENCE.

 9
Author: tech,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2013-10-25 08:25:40

Jeśli możesz liczyć na okres, w którym tabela jest w stanie stabilnym, bez nowych wstawek, powinno to zrobić (nieprzetestowane): 

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;

Pozwala to uzyskać sekwencję z powrotem zsynchronizowaną z tabelą, bez upuszczania/odtwarzania / ponownego przyznawania sekwencji. Nie używa również DDL, więc nie są wykonywane implicit commit. Oczywiście, będziesz musiał wytropić i uderzyć ludzi, którzy nalegają, aby nie używać sekwencji do wypełniania kolumny...

 9
Author: DCookie,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-04-16 01:46:08

W moim przypadku mam sekwencję o nazwie PS_LOG_SEQ, która miała LAST_NUMBER = 3920.

Następnie zaimportowałem niektóre dane z {[4] } do mojej lokalnej Maszyny i wstawiłem do Tabeli PS_LOG. Dane produkcyjne zawierały więcej niż 20000 wierszy, a ostatni LOG_ID (klucz podstawowy) to 20070. Po zaimportowaniu próbowałem wstawić nowe wiersze w tej tabeli, ale podczas zapisywania dostałem wyjątek taki jak ten: 

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

Z pewnością ma to związek z sekwencją PS_LOG_SEQ związaną z PS_LOG tabelą. LAST_NUMBER kolizja z danymi, które zaimportowałem, które używały już następnej wartości ID z PS_LOG_SEQ.

Aby rozwiązać ten problem użyłem tej komendy do aktualizacji sekwencji do najnowszej \ max(LOG_ID) + 1:

alter sequence PS_LOG_SEQ restart start with 20071;

To polecenie resetuje wartość LAST_NUMBER, a następnie mogę wstawić nowe wiersze do tabeli. Koniec kolizji. :)

Uwaga: to alter sequence polecenie jest nowe w Oracle 12c.

 5
Author: Leniel Maccaferri,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2014-06-20 03:56:18