Лучший способ reset последовательности Oracle для следующего значения в существующем столбце?

Ответ 1

Эти две процедуры позволяют мне reset последовательность и reset последовательность, основанную на данных в таблице (извинения за соглашения о кодировании, используемые этим клиентом):

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;

Ответ 2

Вы можете временно увеличить размер кеша и выбрать один фиктивный выбор, а затем reset размер кэша до 1. Итак, например

ALTER SEQUENCE mysequence INCREMENT BY 100;

select mysequence.nextval from dual;

ALTER SEQUENCE mysequence INCREMENT BY 1;

Ответ 3

Если вы можете рассчитывать на период времени, когда таблица находится в стабильном состоянии без новых вставок, это должно сделать это (непроверено):

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;

Это позволяет вам синхронизировать последовательность с таблицей, не отбрасывая/воссоздавая/повторно предоставляя последовательность. Он также не использует DDL, поэтому никаких неявных коммитов не выполняется. Конечно, вам придется выследить и пощекотать людей, которые настаивают на том, чтобы не использовать последовательность, чтобы заполнить столбец...

Ответ 4

С оракулом 10.2g:

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

задает текущее значение последовательности max (pk) вашей таблицы (т.е. следующий вызов NEXTVAL даст вам правильный результат); если вы используете Toad, нажмите F5, чтобы запустить оператор, а не F9, который выводит на выход (таким образом, останавливая приращение после, обычно, 500 строк). Хорошая сторона: это решение - это только DML, а не DDL. Только SQL и PL-SQL. Плохая сторона: это решение печатает строки вывода max (pk), то есть обычно медленнее, чем решение ALTER SEQUENCE.

Ответ 5

В моем случае у меня есть последовательность с именем PS_LOG_SEQ, которая имела LAST_NUMBER = 3920.

Затем я импортировал некоторые данные из PROD на свою локальную машину и вставил в таблицу PS_LOG. Производственные данные имели более чем 20000 строк с последним LOG_ID (первичным ключом) - 20070. После импорта я попытался вставить новые строки в эту таблицу, но при сохранении я получил исключение, подобное этому:

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

Несомненно, это связано с последовательностью PS_LOG_SEQ, связанной с таблицей PS_LOG. LAST_NUMBER сталкивался с импортированными данными, которые уже использовали следующее значение ID из PS_LOG_SEQ.

Чтобы решить эту проблему, я использовал эту команду для обновления последовательности до последней версии \ max(LOG_ID) + 1:

alter sequence PS_LOG_SEQ restart start with 20071;

Эта команда reset значение LAST_NUMBER, и я мог бы затем вставить новые строки в таблицу. Больше никаких столкновений.:)

Примечание: эта команда alter sequence является новой в Oracle 12c.