0

I have a (Oracle) sequence that I use to assign an order number to new orders being inserted into a database table. It started at 1, and increments by 1 for each new order.

I have a new requirement saying that order numbers should start at 10000000 (ten million), so I want to update all existing records (by adding 10000000; order 1 will then be 10000001, order 7002 will be 10007002, etc), and also increment the Oracle sequence so that the next value will be correct.

I was going to simply run this script:

DECLARE
  l_temp NUMBER;
BEGIN
  FOR idx IN 1..10000000 LOOP
    SELECT MY_ORDER_SEQ.nextval
      INTO l_temp
      FROM dual
    ;
  END LOOP;
END;
/

but I don't really know how long that would take to run.

Is there a better way to do this?

osullic
  • 543
  • 1
  • 8
  • 21
  • 2
    Is dropping and re-creating the sequence an option? – Aleksej Aug 01 '19 at 13:47
  • @Aleksej sure. And in fact, that seems so obvious. – osullic Aug 01 '19 at 13:47
  • 1
    The other possibility is to change the increment step of the existing sequence to 10000000, call nextval on it and then change it back to 1. This way the curval will meet the data in your table and you will omit dup_val_on_index problems. – Radagast81 Aug 01 '19 at 13:51

1 Answers1

1

just drop and create new one

drop sequence MY_ORDER_SEQ

and

CREATE SEQUENCE MY_ORDER_SEQ
 START WITH     10000000 -- or your last value
 INCREMENT BY   1
 NOCACHE
 NOCYCLE;
osullic
  • 543
  • 1
  • 8
  • 21
hotfix
  • 3,376
  • 20
  • 36