Best Way to Reset an Oracle Sequence to the Next Value in an Existing Column

Best way to reset an Oracle sequence to the next value in an existing column?

These two procedures let me reset the sequence and reset the sequence based on data in a table (apologies for the coding conventions used by this client):

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;

How do I reset a sequence in Oracle?

Here is a good procedure for resetting any sequence to 0 from Oracle guru Tom Kyte. Great discussion on the pros and cons in the links below too.

tkyte@TKYTE901.US.ORACLE.COM> 
create or replace
procedure reset_seq( p_seq_name in varchar2 )
is
l_val number;
begin
execute immediate
'select ' || p_seq_name || '.nextval from dual' INTO l_val;

execute immediate
'alter sequence ' || p_seq_name || ' increment by -' || l_val ||
' minvalue 0';

execute immediate
'select ' || p_seq_name || '.nextval from dual' INTO l_val;

execute immediate
'alter sequence ' || p_seq_name || ' increment by 1 minvalue 0';
end;
/

From this page: Dynamic SQL to reset sequence value

Another good discussion is also here: How to reset sequences?

Reset sequence to a specific value

You can use a negative increment to reset a sequence to a lower value - this script (it's just a PL/SQL block version of yours) will work with sequence values larger than 9999 without problems):

declare
currval pls_integer;
diff pls_integer;
begin
select SQ_USER_ID.nextval into currval from dual;
dbms_output.put_line('value before alter: ' || currval);
diff := 99999 - currval;
dbms_output.put_line('diff: ' || diff);
execute immediate ' alter sequence SQ_USER_ID INCREMENT BY ' || diff || 'nocache';
select SQ_USER_ID.nextval into currval from dual;
dbms_output.put_line('value after alter: ' || currval);
execute immediate 'alter sequence SQ_USER_ID INCREMENT BY 1 cache 20';
end;

Oracle 9 - Resetting Sequence to match the state of the table

If ID is the name of your PK column and PK_SEQ is the name of your sequence:

  1. Find the value of the highest PK by
    SELECT MAX(ID) FROM tableName

  2. Find the value of the next PK_SEQ by
    SELECT PK_SEQ.NEXTVAL FROM DUAL

  3. If #2 > #1 then nothing needs to be
    done, assuming you treat these
    values as true surrogate keys
  4. Otherwise, alter the sequence to
    jump to the max ID by ALTER SEQUENCE
    PK_SEQ INCREMENT BY [#1 value - #2
    value]
  5. Bump the sequence by SELECT
    PK_SEQ.NEXTVAL FROM DUAL

  6. Reset the sequence increment value
    to 1 by ALTER SEQUENCE PK_SEQ
    INCREMENT BY 1

This all assumes that you don't have new inserts into the table while you're doing this...

Need to reset the value of sequence in Oracle

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>

Get oracle sequenve next value without change it

You should definitely not rely on never missing a value in a sequence, as they optimise for concurrency over sequential numbering. There are quite a few situations in which a number can be "lost".

Furthermore, the value visible in the dba_sequences may not be the actual next value, as the numbers are assigned from an in-memory cache. The underlying sequence metadata table has no data on the usage of that cache. You should also bear in mind that in a RAC system each instance has its own cache of sequence numbers.

You might describe the problem you are trying to solve, as it could be that sequences are not an appropriate mechanism for you.

Oracle SQL: Restart sequence based on row

This reads like a gaps-and-islands problem. I would suggest a window count of "start"s to build the groups:

select t.*,
row_number() over(partition by grp order by batchnr) as seq_no
from (
select t.*,
sum(case when series = 'start' then 1 else 0 end) over(order by batchnr) as grp
from mytable t
) t


Related Topics



Leave a reply



Submit