如何将Oracle序列重置为现有列中的下一个值？

7 浏览
0 Comments

如何将Oracle序列重置为现有列中的下一个值？

由于某种原因，过去的人们在插入数据时没有使用sequence.NEXTVAL。因此，当我使用sequence.NEXTVAL来填充表格时，会出现主键冲突，因为该数字已经在表格中使用。

我该如何更新下一个可用的值？目前，我只是不断地重复插入直到成功（INSERT INTO tbl (pk) VALUES (sequence.NEXTVAL)），这样可以同步更新nextval的值。

0

3 答案

0 Comments

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

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;

这在11g上工作吗？我得到了“未知的设置选项~~~”的错误。

0
0 Comments

问题的出现原因：在导入数据后，尝试向表中插入新的行时，出现了唯一约束冲突的异常。这是由于序列"PS_LOG_SEQ"的"LAST_NUMBER"与导入的数据中已经使用了下一个ID值所引起的。

解决方法：使用以下命令将序列更新为最新的"max(LOG_ID) + 1"值：

alter sequence PS_LOG_SEQ restart start with 20071;

。这个命令重置了"LAST_NUMBER"的值，然后可以向表中插入新的行，避免了冲突。

需要注意的是，这个"alter sequence"命令是在Oracle 12c中新增的功能。此外，虽然这个功能存在于Oracle 18c中，但并未被官方文档记录，可能是为了内部使用。在Oracle 19c中仍然有效，且在语言参考文档中有记录。不过，对于IDENTITY序列（即GENERATED序列），该方法无效。

0
0 Comments

问题的原因是需要将Oracle序列重置为现有列中的下一个值。解决方法是通过临时增加缓存大小，进行一次虚拟查询，然后将缓存大小重置为1。具体操作如下：

ALTER SEQUENCE mysequence INCREMENT BY 100;
select mysequence.nextval from dual;
ALTER SEQUENCE mysequence INCREMENT BY 1;

需要注意在更改序列时，确保没有其他人正在使用该序列。

如果只是想将起始值重置为一个特定数值，可以参考Leniel Macaferi的回答：alter sequence <seq> restart start with <num>;

需要注意的是，缓存大小和增量不同，可以参考链接stackoverflow.com/a/55576009/346534。

以上就是解决Oracle序列重置问题的最佳方法。

0