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

Question

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

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

Answer 1

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

1. 通过以下方式查找最高 PK 的值
   SELECT MAX(ID) FROM tableName

2. 通过以下方式查找下一个 PK_SEQ 的值
   SELECT PK_SEQ.NEXTVAL FROM DUAL

3. 如果 #2 > #1 那么什么都不需要
   完成，假设你对待这些
   值作为真正的代理键
4. 否则，将序列更改为
   通过 ALTER SEQUENCE 跳转到最大 ID
   PK_SEQ 增量 [#1 值 - #2
   value]

5. 通过 SELECT 改变序列
   PK_SEQ.NEXTVAL FROM DUAL

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

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

Answer 2

简而言之，进行游戏：

-- 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.

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

Answer 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;

Answer 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;

Answer 5

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

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

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

Answer 6

添加到 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;

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

所有序列的 Oracle 文档...。

Answer 7

• 从表中选择最大值为其设置序列值。

SELECT setval( 'table_id_seq_name', (SELECT MAX(id) FROM table_name ) );