Manually forward a sequence - oracle sql

Question

I need to forward a set of sequences with only DML access. Due to a bug in a piece of code several values were grabbed without a sequence but instead manually, so now the sequence is duplicating those values. So, I would like to push the sequence to the max value so that the next time nextval is called, it gives a value higher than the maximum. I've got about 50 sequences that each have to go a few thousand forward.

Is this possible with only DML access? If so, how should I go about it?

score 12 · Answer 1 · answered Feb 03 '14 at 20:02

You should determine the difference between the next value of the sequence and the required value. The required value is typically the max value of a primary key column (let's name it ID).

DECLARE
    maxid NUMBER;
    maxseq NUMBER;
    temp NUMBER;  -- without this variable Oracle would skip to query the sequence
BEGIN
    SELECT MAX(ID) INTO maxid FROM MYTABLE;
    SELECT MYSEQ.NEXTVAL INTO maxseq FROM DUAL;
    FOR i IN maxseq .. maxid LOOP
        SELECT MYSEQ.NEXTVAL INTO temp FROM DUAL;
    END LOOP;
END;
/

score 10 · Accepted Answer · edited Oct 17 '17 at 14:38

You can use dynamic SQL to do this. For example, this bit of code will select the next 10,000 values from each of a list of sequences.

DECLARE
  l_num INTEGER;
BEGIN
  FOR seq IN (select * 
                from all_sequences
                where sequence_name in (<<list of 50 sequences>>) 
                  and sequence_owner = <<owner of sequences>>)
  LOOP
    FOR i IN 1 .. 10000
    LOOP
      execute immediate 
         'select ' || seq.sequence_owner || '.' || seq.sequence_name || '.nextval from dual'
         into l_num;
    END LOOP;
  END LOOP;
END;

If you had the ability to issue DDL against the sequence, you could use a similar approach to set the INCREMENT to 10,000, select one value from the sequence, and set the INCREMENT back down to 1 (or whatever it is now).

This is brilliant. I can calculate the number of loops needed for each sequence by subtracting the sequence's next value from the relevant column's max value to do this automatically. I didn't realize that sql had such functionality. Thank you! — Jeremy, Mar 07 '12 at 22:10

score 2 · Answer 3 · answered Jan 02 '15 at 20:41

2

If you have a table with at least as many rows as the amount you want to add to your sequences, the following will work. This increments each sequence by the same amount, which may not suit you, but it's quick and easy without requiring PL/SQL or the need to drop/re-create the sequence. I use it all the time when I want to get development server sequences ahead of production.

SELECT seq1.nextval, seq2.nextval, ..., seqN.nextval
  FROM very_large_table
 WHERE ROWNUM <= number_of_rows_to_add

answered Jan 02 '15 at 20:41

helrich

1,300
1
15
34

This worked great for me. I am using the sequence as a primary key for a table that was loaded manually so the sequence fell behind. I was able to just select nextval on the sequence for each record in the table I'm using on and it's ahead again. – wheeleruniverse Jul 16 '18 at 12:22

score 2 · Answer 4 · answered Mar 07 '12 at 21:40

2

you can just

select seq.nextval from dual

until it is big enough...

answered Mar 07 '12 at 21:40

Randy

16,480
1
37
55

I've got about 50 sequences that each have to go a few thousand forward. – Jeremy Mar 07 '12 at 21:50

score 1 · Answer 5 · answered Feb 12 '21 at 11:09

you can just;

declare
  l_MaxVal  pls_integer;
  l_Currval pls_integer default - 1;
begin
  select max(id)
    into l_MaxVal
    from people;
  while l_Currval < l_Maxval
  loop
    select my_seq.nextval
      into l_Currval
      from dual;
  end loop;
end;

score 1 · Answer 6 · answered Mar 07 '12 at 22:03

To restart the sequence at a different value you need to drop and recreate it.

See the Oracle docs for ALTER SEQUENCE here.

And for CREATE SEQUENCE here

So, no I don't think it's possible with DML access, unless you just increment repeatedly like Randy suggests.

score 0 · Answer 7 · answered Aug 05 '20 at 13:40

There's a trick you can use to select a sequence of numbers, one per row. For example:

SELECT ROWNUM FROM DUAL CONNECT BY ROWNUM <= 100

Replace ROWNUM with <sequence>.NEXTVAL to fast-forward the sequence in large steps, for instance:

SELECT <sequence>.NEXTVAL FROM DUAL CONNECT BY ROWNUM <= 100

I wouldn't use this for making changes to a production database, but for a dev database, it may be sufficient.

Manually forward a sequence - oracle sql

7 Answers7