Need to reset sequence values ​​in Oracle

Question

I work with Spring and Hibernate to develop Java web applications. Suppose I have a table. When I delete some entries from this table, sometimes I need to reset the value of the primary key field.

Say 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 start at 6 , but it starts at 11 .

If I need to run 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 auto_increment value to a maximum + 1 value of this field (perhaps conceptually incorrect, but it works).

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

Answer 1

The reasons why you should not reset the value if it is 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 free sequence of spaces of numbers , perfect 1, 2 .. n.

If you call .nextval and do not use the value that it is gone. Are you going to reset and recreate the sequence? If you run the insert and cancel it, and Oracle rollback , then what you did, these values ​​disappeared. If you install nocache , then you will have fewer spaces, but with the price of getting into performance; Is it worth it?

The cache should indicate the number of attachments that you expect to make at any time for all sessions in order to avoid any performance issues. The sequences are designed to provide a very fast scalable way to create a surrogate key without locks, etc., so as not to generate a set of positive integers.

At the end of the day, this does not matter at all. If you rely on a continuous sequence as the key to your table, then you have a problem with your data, not the sequences.

---

Answering the question:

To answer your question you need:

• First, find out what is the maximum id (sequence) value in your table.
• Then discard and recreate the sequence .

Finding the maximum value means that you will need to recreate the sequence dynamically at the expense of another performance hit.

If you try to insert something into your table while this happens, it will fail and can invalidate any triggers or other objects that 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 said, this is not recommended, and you should just ignore any spaces.

---

Update - aka Best Answer Thanks Jeffrey Kemp :

Unlike the documentation recommendation, as Jeffrey Kemp suggested in the comments, the way to do this is without discarding or re-creating the sequence.

Namely: by:

• Develop the difference between the maximum id in your table and the current sequence value.
• Sequence change to increase by this negative number
• Change the sequence to increase by 1 again.

The advantages of this are that the object still exists, and triggers, grants, etc. still supported. The disadvantage, as I see it, is that if another session increases by this negative number at the same time as yours, you may go back too far.

Here's a demo:

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. 

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

Answer 2

The following also works (increases the value of your sequence by 100):

 select my_sequence.nextval from dual connect by level <= 100; 

Answer 3

Regarding the Oracle ALTER SEQUENCE documentation,

• To restart a sequence with a different number, you must recreate it.

You need to pass the current maximum value of the primary key column in order to generate the next sequence number.
If you continue to delete some recent records and want to reuse the sequence values ​​that you already created, you may need to restart this sequence again and again. Deleting a sequence can result in an SQLException if the server receives a request for the next value before the sequence is ready for service.

You can also find helpful notes on Oracle Sequences here .

Answer 4

This trick works for me. In my sequence (USERSUTPL_USERUTPL_SQ) the last number was 53. The last id in my table was 83

 SELECT MAX(ID) FROM USERSUTPL_USERUTPL 

Then 83 - 53 = 31. So:

 ALTER SEQUENCE USERSUTPL_USERUTPL_SQ INCREMENT BY +31; SELECT USERSUTPL_USERUTPL_SQ.NEXTVAL FROM dual; ALTER SEQUENCE USERSUTPL_USERUTPL_SQ INCREMENT BY 1; 

And its changes last number .: D