需要重置Oracle中序列的值 [英] Need to reset the value of sequence in Oracle

问题描述

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

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



如果我需要在 6 （最大值）处启动主键值+1 ）在MySql中，我只需要执行下面的SQL语句：



  alter table table_name AUTO_INCREMENT = 1; 
  

这会自动重置 auto_increment 到最大值+ 1 该字段的值（可能在概念上不正确，但它有效）。



在Oracle（10g）中，我在主键上使用 sequence 。在从Oracle中删除某些记录时，Oracle是否可以将序列的值重置为 maximum + 1 值数据库？

解决方案

原因为什么在使用时不应重置该值：

如果您有20条记录并删除记录5-10，会发生什么情况？你在中间有一个缺口，重新设定序列不会解决。序列将 从不生成一个无间隙的数字序列，这是一个完美的 1,2 ... n 。

如果您调用 .nextval 并且不使用它的值 。你打算放弃并重新创建序列吗？如果您开始插入并取消它并且Oracle 回滚你所做的那些价值观消失了。如果你设置 nocache ，那么你将会有较少的差距，但是以牺牲性能为代价;这值得么？

您的缓存应设置为您希望在任何时间执行的插入次数 跨所有会话 以避免任何性能问题。序列旨在提供一种非常快速，可扩展的创建代理键的方式，而不用任何锁等。 not 可重新生成一组正整数。

在一天结束时，丝毫不应该有任何问题。如果你依赖于一个不间断的序列作为你的表的关键，那么你的数据有问题而不是序列。

---

回答问题：

要真正回答您的问题，您需要：

$ b $首先，找出表中最大的id（序列）值是什么。 然后删除并重新创建序列。

找到最大值意味着您需要动态地重新创建序列，但需要牺牲另一个命中性能。

如果在发生这种情况时尝试向表中插入内容，它将失败，并且可能会使使用该序列的任何触发器或其他对象失效：

declare

l_max_value number;

begin

从my_table中选择max（id）
到l_max_value
;

立即执行'drop sequence my_sequence_name';

- 如果您插入多于
- 一次一行，或者以任何速度插入，则不推荐使用nocache。
立即执行'创建序列my_sequence_name
以'||开头l_max_value
|| '增加1
nomaxvalue
nocycle
nocache';

end;
/


正如我所说这不是建议的，您应该忽略任何差距。 / p>

---

更新 - 又名更好的答案感谢 Jeffrey Kemp ：

与文档建议相反，正如Jeffrey Kemp在评论中所建议的那样没有删除并重新创建序列。

即，通过：



1. 计算最大值<$你的表中的c $ c> id 和序列的当前值。


2. 将序列改为按此负数递增
   
3. 改变序列以再次递增1。

这样做的好处是对象仍然存在，触发器，授权等仍然保留。正如我所看到的，缺点是，如果另一个会话与此同时以负数增加，则可能会回到过度。

下面是一个演示：

设置测试：

  SQL>创建序列test_seq 
 2以1 
开始3增加1 
 4 nomaxvalue 
 5 nocycle 
 6 nocache; 
 
创建序列。 
 
 SQL> 
 SQL>创建表tmp_test（id号（16））; 
 
创建表。 
 
 SQL> 
 SQL>申报
 2 l_nextval号码; 
 3 begin 
 4 
 5 for i in 1 .. 20 loop 
 6插入tmp_test值（test_seq.nextval）; 
 7结束循环; 
 8 
 9 end; 
 10 / 
 
 PL / SQL过程已成功完成。 
 
 SQL> 
 SQL>从dual中选择test_seq.currval; 
 
 CURRVAL 
 ---------- 
 20 
 
 SQL> 
 SQL>从tmp_test中删除，其中id> 15; 
 
删除了5行。 
 
 SQL>承诺; 
 
完成提交。 
  

恢复顺序

  SQL> 
 SQL>申报
 2 
 3 l_max_id编号; 
 4 l_max_seq number; 
 5 
 6 begin 
 7 
 8  - 获取最大ID 
 9从tmp_test中选择max（id）到l_max_id 
 10; 
 11 
 12  - 获取当前序列值; 
 13从双重选择test_seq.currval到l_max_seq 
 14; 
 15 
 16  - 改变序列以增加差值（本例中为-5）
。 
 17立即执行'alter sequence test_seq 
 18增加'|| （l_max_id_l_max_seq）; 
 19 
 20  - 'increment'by -5 
 21将test_seq.nextval选择为l_max_seq 
 22 from dual; 
 23 
 24  - 将序列改回正常
 25立即执行'alter sequence test_seq 
 26增加1'; 
 27 
 28 end; 
 29 / 
 
 PL / SQL过程已成功完成。 
 
 SQL> 
 SQL>从dual中选择test_seq.currval; 
 
 CURRVAL 
 ---------- 
 15 
 
 SQL> 
  

I'm working with Spring and Hibernate to develop web applications in Java. Let's assume that I have a table. When I delete some records from this table, sometimes I need to reset the value of the primary key field.

Let's say that I have 10 records in a table and I delete the last 5 records. Now, when I insert new records, the value of the primary key field should be started at 6 but it would start at 11.

If I need to start the primary key value at 6 (maximum +1) in MySql, I just need to execute the following SQL statement.

alter table table_name auto_increment=1;

This will automatically reset the value of auto_increment to maximum + 1 value of that field (May conceptually be incorrect but it works).

In Oracle (10g), I'm using sequence with the primary key. Is there a way in Oracle to reset the value of the sequence to maximum + 1 value when some records are deleted from the database?

解决方案

Reasons why you shouldn't reset the value if it's being used:

What happens if you have 20 records and delete records 5-10? You have a gap in the middle that re-setting the sequence will not solve. Sequences will never generate a gap free sequence of numbers, a perfect 1, 2 .. n.

If you call .nextval and don't use the value it's gone. Are you going to drop and re-create the sequence? If you start an insert and cancel it and Oracle rolls back what you've done those values are gone. If you set nocache then you will have less gaps but at a cost of a hit to performance; is it worth it?

Your cache should be set to the number of inserts you expect to do at any one time across all sessions to avoid any performance issues. Sequences are designed to provide a very quick, scalable way of creating a surrogate key without any locks etc not to re-generate the set of positive integers.

At the end of the day it shouldn't matter in the slightest. If you're relying on an unbroken sequence as the key of your table then you have a problem with your data rather than sequences.

---

Answering the question:

To actually answer your question you would need to:

1. Firstly, find out what the maximum id (sequence) value in your table is.
2. Then drop and re-create the sequence.

Finding the maximum value means you'd need to re-create the sequence dynamically at the cost of another hit to performance.

If you try to insert something into your table whilst this is happening it will fail, and may invalidate any triggers or other objects which use the sequence:

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

As I say this is not recommended and you should just ignore any gaps.

---

Update - aka A Better Answer Thanks to Jeffrey Kemp:

Contrary to the documentation's recommendation there is, as Jeffrey Kemp suggested in the comments, a way to do this without dropping and re-creating the sequence.

Namely, by:

1. Working out the difference between the maximum id in your table and the current value of the sequence.
2. Altering the sequence to increment by this negative number
3. Altering the sequence to increment by 1 again.

The benefits of this are that the object still exists so and triggers, grants etc are still maintained. The downside, as I see it, is that if another session increments by this negative number at the same time as yours you can go back too far.

Here's a demonstration:

Set up the test:

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.

Revert the sequence

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>

这篇关于需要重置Oracle中序列的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！