Oracle 9 - 重置序列以匹配表的状态

Oracle 9 - Resetting Sequence to match the state of the table

提问by AJM

I have a sequence used to seed my (Integer based) primary keys in an oracle table.

我有一个序列用于在 oracle 表中播种我的（基于整数的）主键。

It appears this sequence has not always been used to insert new values into the table. How do I get the sequence back in step with the actual values in the table?

似乎此序列并不总是用于将新值插入表中。如何使序列与表中的实际值保持一致？

采纳答案by dpbradley

If ID is the name of your PK column and PK_SEQ is the name of your sequence:

如果 ID 是您的 PK 列的名称，而 PK_SEQ 是您的序列的名称：

1. Find the value of the highest PK by SELECT MAX(ID) FROM tableName

2. Find the value of the next PK_SEQ by SELECT PK_SEQ.NEXTVAL FROM DUAL

3. If #2 > #1 then nothing needs to be done, assuming you treat these values as true surrogate keys
4. Otherwise, alter the sequence to jump to the max ID by ALTER SEQUENCE PK_SEQ INCREMENT BY [#1 value - #2 value]

5. Bump the sequence by SELECT PK_SEQ.NEXTVAL FROM DUAL

6. Reset the sequence increment value to 1 by ALTER SEQUENCE PK_SEQ INCREMENT BY 1

1. 通过 SELECT MAX(ID) FROM tableName 找到最高 PK 的值

2. 通过 SELECT PK_SEQ.NEXTVAL FROM DUAL 找到下一个 PK_SEQ 的值

3. 如果 #2 > #1 那么什么都不用做，假设您将这些值视为真正的代理键
4. 否则，通过 ALTER SEQUENCE PK_SEQ INCREMENT BY [#1 value - #2 value] 更改序列以跳转到最大 ID

5. 通过 SELECT PK_SEQ.NEXTVAL FROM DUAL 颠倒序列

6. 通过 ALTER SEQUENCE PK_SEQ INCREMENT BY 1 将序列增量值重置为 1

This all assumes that you don't have new inserts into the table while you're doing this...

这一切都假设您在执行此操作时没有新的插入表...

回答by David Andres

In short, game 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.

You can get the max sequence value used within your table, do the math, and update the sequence accordingly.

您可以获得表格中使用的最大序列值，进行数学运算，并相应地更新序列。

回答by Dieter DHoker

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;

回答by Blama

I made this script as I did not find a script online that dynamically sets allmy sequences to the current highest ID. Tested on Oracle 11.2.0.4.

我制作了这个脚本，因为我没有在网上找到一个可以将我的所有序列动态设置为当前最高 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;

回答by Jim Hudson

In some cases, you may find it easier to simply get the current max value and then

在某些情况下，您可能会发现简单地获取当前最大值然后

drop sequence x;
create sequence x start with {current max + 1};

The app will be broken after you do the drop. But that will keep anybody from inserting rows during that period, and creating a sequence is quick. Make sure you recreate any grants on the sequence since those will be dropped when the sequence is. And you may want to manually recompile any plsql that depends on the sequence.

执行drop后，该应用程序将被破坏。但这将阻止任何人在此期间插入行，并且创建序列很快。确保在序列上重新创建任何授权，因为在序列创建时这些授权将被删除。并且您可能希望手动重新编译任何依赖于序列的 plsql。

回答by 1737973

Adding up to https://stackoverflow.com/a/15929548/1737973, but without resorting to SEQUENCENAME.NEXTVALhence not resulting in one position over it should be:

加起来https://stackoverflow.com/a/15929548/1737973，但不诉诸于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;

Disclaimer: if the sequence is using caching (all_sequences.cache_sizeset to bigger than 0) you are probably wanting to take it into consideration in theCompute the difference step.

免责声明：如果序列使用缓存（all_sequences.cache_size设置为大于 0），您可能希望在计算差异步骤中考虑它。

Oracle documentation for all sequences....

Oracle文档all sequences...。