需要在Oracle中重置序列的值

一尘不染

需要在Oracle中重置序列的值

spring

我正在与Spring和Hibernate一起使用Java开发Web应用程序。假设我有一张桌子。当我从该表中删除一些记录时，有时我需要重置主键字段的值。

假设我在一个表中有10条记录，而我删除了最后5条记录。现在，当我插入新记录时，主键字段的值应从开始，6但应从开始11。

如果需要在MySql 中的6（maximum +1）处启动主键值，则只需执行以下SQL语句。

alter table table_name auto_increment=1;

这将自动重置价值auto_increment，以maximum + 1该字段的值（在概念是不正确的，但它的工作原理）。

在Oracle（10g）中，我使用sequence主键。从数据库中删除某些记录后，Oracle中有没有一种方法可以将sequenceto的maximum + 1值重置为value ？

阅读 819

2020-04-13

共1个答案

一尘不染

如果使用该值，则不应重置该值的原因：
如果你有20条记录并删除5-10条记录会怎样？中间有一个缝隙，无法重新设置序列。序列永远不会生成无间隙的数字序列，即完美的1、2 .. n。

如果你调用.nextval并且不使用该值，它就消失了。你要删除并重新创建序列吗？如果开始插入并取消插入，Oracle 将回滚所做的操作，这些值将消失。如果你设置了该选项，nocache则差距将较小，但会降低性能。这值得么？

你的缓存应设置为希望在所有会话中一次执行的插入次数，以避免任何性能问题。序列旨在提供一种非常快速，可扩展的方式来创建代理密钥，而无需任何锁等，以免重新生成正整数集。

归根结底，这丝毫没有关系。如果你将不间断的序列作为表的键，则你的数据而不是序列会出现问题。

回答问题：
要实际回答你的问题，你需要：

首先，找出表中的最大id（序列）值。
然后放下并重新创建序列。
找到最大值意味着你需要动态地重新创建序列，而又要牺牲性能。

如果在这种情况下尝试将某些东西插入表中，它将失败，并且可能会使使用该序列的任何触发器或其他对象无效：

declare

   l_max_value number;

begin

   select max(id)
     into l_max_value
     from my_table;

   execute immediate 'drop sequence my_sequence_name';

   -- nocache is not recommended if you are inserting more than
   -- one row at a time, or inserting with any speed at all.
   execute immediate 'create sequence my_sequence_name
                           start with ' || l_max_value
                      || ' increment by 1
                           nomaxvalue
                           nocycle
                           nocache';

end;
/

正如我所说，不建议这样做，你应该忽略任何差距。

更新-又名更好的答案感谢Jeffrey Kemp：
正如杰弗里·肯普（Jeffrey Kemp）在评论中所建议的那样，有一种与文档建议相反的方法，该方法无需删除并重新创建序列即可。

即，通过：

计算id表中的最大值与序列的当前值之间的差。
更改顺序以此负数递增
更改顺序以再次增加1。
这样做的好处是对象仍然存在，并且触发器，授权等也得以维护。如我所见，其不利之处在于，如果另一个会话与你的会话同时增加此负数，则你可能退得太远。

这是一个示范：

设置测试：

SQL> create sequence test_seq
  2   start with 1
  3   increment by 1
  4   nomaxvalue
  5   nocycle
  6   nocache;

Sequence created.

SQL>
SQL> create table tmp_test ( id number(16) );

Table created.

SQL>
SQL> declare
  2     l_nextval number;
  3  begin
  4
  5    for i in 1 .. 20 loop
  6       insert into tmp_test values ( test_seq.nextval );
  7    end loop;
  8
  9  end;
 10  /

PL/SQL procedure successfully completed.

SQL>
SQL> select test_seq.currval from dual;

   CURRVAL
----------
        20

SQL>
SQL> delete from tmp_test where id > 15;

5 rows deleted.

SQL> commit;

Commit complete.

还原顺序

SQL>
SQL> declare
  2
  3     l_max_id number;
  4     l_max_seq number;
  5
  6  begin
  7
  8     -- Get the maximum ID
  9     select max(id) into l_max_id
 10       from tmp_test;
 11
 12     -- Get the current sequence value;
 13     select test_seq.currval into l_max_seq
 14       from dual;
 15
 16     -- Alter the sequence to increment by the difference ( -5 in this case )
.
 17     execute immediate 'alter sequence test_seq
 18                          increment by ' || ( l_max_id - l_max_seq );
 19
 20     -- 'increment' by -5
 21     select test_seq.nextval into l_max_seq
 22       from dual;
 23
 24     -- Change the sequence back to normal
 25     execute immediate 'alter sequence test_seq
 26                          increment by 1';
 27
 28  end;
 29  /

PL/SQL procedure successfully completed.

SQL>
SQL> select test_seq.currval from dual;

   CURRVAL
----------
        15

SQL>

2020-04-13