Сбросить или изменить значение sequence в Oracle: методы

Быстрый ответ

Чтобы сбросить последовательность в Oracle, используйте команду ALTER SEQUENCE, применимую с некоторым изобретательством через отрицательный инкремент:

ALTER SEQUENCE my_seq INCREMENT BY -currval MINVALUE 0; -- Будьте внимательны, чтобы не повредить данные.
SELECT my_seq.NEXTVAL FROM dual; -- Устанавливаем минимально возможное значение.
ALTER SEQUENCE my_seq INCREMENT BY 1 START WITH 100; -- Теперь начинаем заново на чистовом листе!

Переменную currval замените на текущее максимальное значение последовательности, которое вы хотите сбросить. В результате, следующее значение my_seq.NEXTVAL будет равно 100.

Удобные альтернативы

Приведенный выше подход хорош для единичных сбросов, но что делать, если требуется автоматическое и динамическое решение? В это случае рекомендуется элегантный метод, предложенный авторитетным Томом Кайтом:

DECLARE
  val NUMBER;
BEGIN
  SELECT max(column_name) INTO val FROM table_name; -- Выберите подходящую таблицу и столбец!
  EXECUTE IMMEDIATE 'ALTER SEQUENCE my_seq INCREMENT BY -' || val || ' MINVALUE 0'; -- Сначала мы идем вниз, словно со склона горы.
  EXECUTE IMMEDIATE 'SELECT my_seq.NEXTVAL FROM dual'; -- Доходим до самого "дна".
  EXECUTE IMMEDIATE 'ALTER SEQUENCE my_seq INCREMENT BY 1 START WITH ' || val+1; -- И снова идем вверх. Звучит забавно, не правда ли?
END;

А пользователям Oracle 18c и более новых версий даже повезло ещё больше:

ALTER SEQUENCE my_seq RESTART START WITH 1; -- Что может быть проще?

Целостность данных? Кто-нибудь?

Игра со сбросом последовательностей может продолжаться до тех пор, пока оно не повредит ваши данные. Крайне важно учесть последствия удаления и создания последовательностей в контексте существующих записей.

Визуализация

"Лучше один раз увидеть, чем сто раз услышать". По аналогии представьте себе тысячу приращений:

Текущее значение последовательности: 🔢🕒 55 
Желаемое значение для сброса: 🔢🕒 0

ALTER SEQUENCE my_sequence RESTART WITH 0; -- Сбрасываем и начинаем сначала.

Статус последовательности: | До: 🔢🕒 55 | После: 🔢🕒 0 |

Осуществляем новый старт последовательности с нуля, начиная новый цикл приращений.

Мастер-класс по сбросу последовательностей

Массовый сброс

Если ваши последовательности потеряли синхронизацию, вы можете восстановить их следующим образом:

CREATE OR REPLACE PROCEDURE Reset_All_Sequences IS -- Эта процедура вернет все ваши последовательности обратно в строй.
BEGIN
  EXECUTE IMMEDIATE 'ALTER SEQUENCE seq1 INCREMENT BY -currval1 MINVALUE 0'; -- Сначала мы идем назад.
  EXECUTE IMMEDIATE 'SELECT seq1.NEXTVAL FROM dual'; -- Затем мы движемся к началу.
  EXECUTE IMMEDIATE 'ALTER SEQUENCE seq1 INCREMENT BY 1'; -- И наконец, мы начинаем заново.
  -- Проделайте те же действия для каждой требуемой последовательности.
END;

Подробнее об этом расскажет наш спикер на видео

Сложные проблемы

Помните, что сброс последовательностей может привести к полной путанице в приложениях. Будьте особенно осторожны с кэшированными последовательностями и состояниями гонки: они могут вызвать проблемы в многопользовательских средах.

Увлечение единорогами

Избегайте использования недокументированных функций – это как преследование мифических существ: они ненадежны и могут навредить стабильности системы.

Полезные материалы

1. ALTER SESSION — Официальная документация Oracle на изменение параметров последовательностей.
2. Ask TOM — Советы экспертов по сбросу последовательностей.
3. Sequence – Oracle FAQ — Обширное руководство по последовательностям в Oracle.
4. sql – Reset auto increment counter in postgres — Сравнительный анализ сброса последовательностей в Oracle и автоинкремента в PostgreSQL.
5. oracle – can't backup — Практические примеры работы с последовательностями в разных версиях Oracle.
6. Resetting Sequence in Oracle Database — Подробная инструкция по восстановлению последовательностей в Oracle.