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




oracle sql get next value from sequence (4)

По какой-то причине люди в прошлом вставляли данные без использования последовательности. NEXTVAL. Поэтому, когда я иду использовать sequence.NEXTVAL, чтобы заполнить таблицу, я получаю нарушение PK, так как это число уже используется в таблице.

Как я могу обновить следующее значение, чтобы оно было пригодным для использования? Прямо сейчас, я просто вставляю снова и снова, пока он не будет успешным ( INSERT INTO tbl (pk) VALUES (sequence.NEXTVAL) ), и это синхронизирует следующий.


В моем случае у меня есть последовательность под названием 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;

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

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


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

ALTER SEQUENCE mysequence INCREMENT BY 100;

select mysequence.nextval from dual;

ALTER SEQUENCE mysequence INCREMENT BY 1;

С оракулом 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.


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

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;




sequence