将 Oracle 序列重置为现有列中的下一个值的最佳方法？

Question

由于某种原因，过去人们插入数据时没有使用sequence.NEXTVAL。因此，当我使用sequence.NEXTVAL来填充表时，我遇到了PK违规，因为该数字已在表中使用。

如何更新下一个值以使其可用？现在，我只是一遍又一遍地插入，直到成功（INSERT INTO tbl (pk) VALUES (sequence.NEXTVAL)），然后同步 nextval。

Answer 1

您可以暂时增加缓存大小并执行一次虚拟选择，然后将缓存大小重置回 1。例如

ALTER SEQUENCE mysequence INCREMENT BY 100;

select mysequence.nextval from dual;

ALTER SEQUENCE mysequence INCREMENT BY 1;

Answer 2

就我而言，我有一个名为 PS_LOG_SEQ 的序列，其中包含 LAST_NUMBER = 3920。

然后，我将一些数据从 PROD 导入到本地计算机，并插入到 PS_LOG 表中。生产数据有超过 20000 行，最新的 LOG_ID（主键）为 20070。导入后，我尝试在此表中插入新行，但在保存时出现如下异常：

ORA-00001: unique constraint (LOG.PS_LOG_PK) violated

当然，这有处理与 PS_LOG 表关联的序列 PS_LOG_SEQ。 LAST_NUMBER 与我导入的数据发生冲突，该数据已使用 PS_LOG_SEQ 中的下一个 ID 值。

为了解决这个问题，我使用此命令将序列更新为最新的 \ max(LOG_ID) + 1：

alter sequence PS_LOG_SEQ restart start with 20071;

此命令重置 LAST_NUMBER 值，然后我可以将新行插入到桌子。不再有碰撞。 :)

注意：这个 alterequence 命令是 Oracle 12c 中的新命令。

注意：此博客post 记录了 ALTER SEQUENCE RESTART 选项确实存在，但截至 18c，没有记录；它显然是供 Oracle 内部使用的。

Answer 3

这两个过程让我重置序列并根据表中的数据重置序列（对此客户使用的编码约定表示歉意）：

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;

Answer 4

如果您可以指望在一段时间内表处于稳定状态，没有进行新的插入，那么这应该可以做到（未经测试）：

DECLARE
  last_used  NUMBER;
  curr_seq   NUMBER;
BEGIN
  SELECT MAX(pk_val) INTO last_used FROM your_table;

  LOOP
    SELECT your_seq.NEXTVAL INTO curr_seq FROM dual;
    IF curr_seq >= last_used THEN EXIT;
    END IF;
  END LOOP;
END;

这使您能够使序列恢复与表同步，而不会丢失/重新创建/重新授予序列。它还不使用 DDL，因此不会执行隐式提交。当然，您将不得不追捕并打那些坚持不使用该序列填充该列的人......

Answer 5

对于 oracle 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 解决方案慢。

Answer 6

如今，在 Oracle 12c 或更高版本中，您可能将列定义为 GENERATED ... AS IDENTITY，并且 Oracle 会自行处理序列。

您可以使用 ALTER TABLE 语句来修改标识的“START WITH”。

ALTER TABLE tbl MODIFY ("ID" NUMBER(13,0) GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 3580 NOT NULL ENABLE);

Answer 7

很抱歉没有单行解决方案，因为我的程序在 Typeorm 和 node-oracle 中运行。不过，我认为以下 SQL 命令将有助于解决这个问题。

从序列中获取LAST_NUMBER。

SELECT SEQUENCE_NAME, LAST_NUMBER FROM ALL_SEQUENCES WHERE SEQUENCE_NAME = '${sequenceName}'

从最后一行获取您的 PK 值（在本例中 ID 是 PK）。

SELECT ID FROM ${tableName} ORDER BY ID DESC FETCH NEXT 1 ROWS ONLY

最后将 LAST_NUMBER 更新为值 + 1：

ALTER SEQUENCE ${sequenceName} RESTART START WITH ${value + 1}

Answer 8

就我而言，我使用了一种方法将序列重置为零，然后将目标表从零设置为最大值：

DECLARE
    last_val NUMBER;
    next_val NUMBER;
BEGIN
    SELECT MAX(id_field) INTO next_val FROM some_table;
    IF next_val > 0 THEN
        SELECT some_table_seq.nextval INTO last_val FROM DUAL;
        EXECUTE IMMEDIATE 'ALTER SEQUENCE some_table_seq INCREMENT BY -' || last_val || ' MINVALUE 0';
        SELECT some_table_seq.nextval INTO last_val FROM DUAL;
        EXECUTE IMMEDIATE 'ALTER SEQUENCE some_table_seq INCREMENT BY ' || next_val;
        SELECT some_table_seq.nextval INTO last_val FROM DUAL;
        EXECUTE IMMEDIATE 'ALTER SEQUENCE some_table_seq INCREMENT BY 1 MINVALUE 1';
    END IF;
END;

Answer 9

我想最好的解决方案是更新序列当前值的存储过程。

我在Oracle中使用这个存储过程：

CREATE OR REPLACE PROCEDURE SEQ_UPDATE_NEXTVAL(seq_name IN VARCHAR2, table_name IN VARCHAR2, col_name IN VARCHAR2)
AS
    colCurrVal   NUMBER;
    seqCurrVal   NUMBER;
BEGIN
    EXECUTE IMMEDIATE 'SELECT MAX(' || col_name || ') AS n FROM ' || table_name INTO colCurrVal;

EXECUTE IMMEDIATE 'SELECT ' || seq_name || '.currval FROM dual' INTO seqCurrVal;

IF (colCurrVal - seqCurrVal) <= 0 THEN
    DBMS_OUTPUT.put_line('Sequence ' || seq_name || ' does not need increment. currval: ' || seqCurrVal);
    return;
END IF;

EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || seq_name || ' increment by ' || (colCurrVal - seqCurrVal);

EXECUTE IMMEDIATE 'SELECT ' || seq_name || '.nextval FROM dual' INTO seqCurrVal;
EXECUTE IMMEDIATE 'SELECT ' || seq_name || '.currval FROM dual' INTO seqCurrVal;

EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || seq_name || ' increment by 1';

DBMS_OUTPUT.put_line('Sequence ' || seq_name || ' currval is now at ' || seqCurrVal);
END;

执行存储过程：（

CALL SEQ_UPDATE_NEXTVAL('SEQUENCE_NAME', 'TABLE_NAME', 'COLUMN_NAME');

对不起我的英语，我同意请修复我）

问候。