Oracle 9 - Сброс последовательности для соответствия состоянию таблицы
У меня есть последовательность, используемая для извлечения первичных ключей на основе Integer в таблице оракула.
Похоже, что эта последовательность не всегда использовалась для вставки новых значений в таблицу. Как вернуть последовательность в шаге с фактическими значениями в таблице?
Ответы
Ответ 1
Если ID - это имя столбца PK, а PK_SEQ - имя вашей последовательности:
-
Найдите значение самого высокого PK на
SELECT MAX (ID) FROM tableName
-
Найдите значение следующего PK_SEQ на SELECT PK_SEQ.NEXTVAL FROM DUAL
- Если # 2 > # 1, то ничего не должно быть если вы рассматриваете эти значения как истинные ключи суррогата
- В противном случае измените последовательность на перейти к максимальному ID с помощью ALTER SEQUENCE PK_SEQ INCREMENT BY [# 1 value - # 2 значение]
-
Выполнить последовательность с помощью SELECT PK_SEQ.NEXTVAL FROM DUAL
-
Reset значение приращения последовательности к 1 с помощью ALTER SEQUENCE PK_SEQ INCREMENT BY 1
Все это предполагает, что у вас нет новых вставок в таблицу, пока вы это делаете...
Ответ 2
Короче говоря, игра it:
-- Current sequence value is 1000
ALTER SEQUENCE x INCREMENT BY -999;
Sequence altered.
SELECT X.NEXTVAL FROM DUAL;
1
ALTER SEQUENCE x INCREMENT BY 1;
Sequence altered.
Вы можете получить максимальное значение последовательности, используемое в вашей таблице, выполнить математику и соответствующим образом обновить последовательность.
Ответ 3
Declare
difference INTEGER;
sqlstmt varchar2(255);
sequenceValue Number;
begin
sqlstmt := 'ALTER SEQUENCE YOURSEQUENCE INCREMENT BY ';
select YOURSEQUENCE.NEXTVAL into sequenceValue from dual;
select (nvl(Max(YOURID),0) - sequenceValue)+1 into difference from YOURTABLE;
if difference > 0 then
EXECUTE IMMEDIATE sqlstmt || difference;
select YOURSEQUENCE.NEXTVAL INTO sequenceValue from dual;
EXECUTE IMMEDIATE sqlstmt || 1;
end if;
end;
Ответ 4
Я сделал это script, так как не нашел script онлайн, который динамически устанавливает все мои последовательности в текущий самый высокий ID. Протестировано на Oracle 11.2.0.4.
DECLARE
difference INTEGER;
sqlstmt VARCHAR2(255) ;
sqlstmt2 VARCHAR2(255) ;
sqlstmt3 VARCHAR2(255) ;
sequenceValue NUMBER;
sequencename VARCHAR2(30) ;
sequencelastnumber INTEGER;
CURSOR allseq
IS
SELECT sequence_name, last_number FROM user_sequences ORDER BY sequence_name;
BEGIN
DBMS_OUTPUT.enable(32000) ;
OPEN allseq;
LOOP
FETCH allseq INTO sequencename, sequencelastnumber;
EXIT
WHEN allseq%NOTFOUND;
sqlstmt := 'ALTER SEQUENCE ' || sequencename || ' INCREMENT BY ';
--Assuming: <tablename>_id is <sequencename>
sqlstmt2 := 'select (nvl(Max(ID),0) - :1)+1 from ' || SUBSTR(sequencename, 1, LENGTH(sequencename) - 3) ;
--DBMS_OUTPUT.PUT_LINE(sqlstmt2);
--Attention: makes use of user_sequences.last_number --> possible cache problems!
EXECUTE IMMEDIATE sqlstmt2 INTO difference USING sequencelastnumber;
IF difference > 0 THEN
DBMS_OUTPUT.PUT_LINE('EXECUTE IMMEDIATE ' || sqlstmt || difference) ;
EXECUTE IMMEDIATE sqlstmt || difference;
sqlstmt3 := 'SELECT ' || sequencename ||'.NEXTVAL from dual';
DBMS_OUTPUT.PUT_LINE('EXECUTE IMMEDIATE ' || sqlstmt3 || ' INTO sequenceValue') ;
EXECUTE IMMEDIATE sqlstmt3 INTO sequenceValue;
DBMS_OUTPUT.PUT_LINE('EXECUTE IMMEDIATE ' || sqlstmt || 1) ;
EXECUTE IMMEDIATE sqlstmt || 1;
DBMS_OUTPUT.PUT_LINE('') ;
END IF;
END LOOP;
CLOSE allseq;
END;
Ответ 5
В некоторых случаях вам может быть проще просто получить текущее максимальное значение, а затем
drop sequence x;
create sequence x start with {current max + 1};
Приложение будет разбито после того, как вы сделаете это. Но это не заставит кого-либо вставлять строки в течение этого периода, и создание последовательности выполняется быстро. Удостоверьтесь, что вы воссоздаете какие-либо гранты в последовательности, поскольку они будут удалены, когда последовательность будет. И вы можете вручную перекомпилировать любой plsql, который зависит от последовательности.
Ответ 6
Добавляем до fooobar.com/info/337875/..., но не прибегая к SEQUENCENAME.NEXTVAL
, следовательно, не приводя к одной позиции над ней:
DECLARE
difference INTEGER;
alter_sequence_statement VARCHAR2 (255);
sequence_value NUMBER;
BEGIN
-- Base for the statement that will set the sequence value.
alter_sequence_statement :=
'ALTER SEQUENCE SEQUENCENAME INCREMENT BY ';
-- Fetch current last sequence value used.
SELECT
-- You could maybe want to make some further computations just
-- below if the sequence is using caching.
last_number
INTO sequence_value
FROM all_sequences
WHERE sequence_owner = 'SEQUENCEOWNER' AND sequence_name = 'SEQUENCENAME';
-- Compute the difference.
SELECT max(id) - sequence_value + 1 INTO difference
FROM SCHEMANAME.TABLENAME;
IF difference <> 0 THEN
-- Set the increment to a big offset that puts the sequence near
-- its proper value.
EXECUTE IMMEDIATE alter_sequence_statement || difference;
-- This 'sequence_value' will be ignored, on purpose.
SELECT SEQUENCENAME.NEXTVAL INTO sequence_value FROM dual;
-- Resume the normal pace of incrementing one by one.
EXECUTE IMMEDIATE alter_sequence_statement || 1;
END IF;
END;
Отказ от ответственности: если последовательность использует кеширование (all_sequences.cache_size
установлено больше 0), вы, вероятно, захотите принять это во внимание при вычислении разностного шага.
Документация Oracle для all sequences
....