Tags

,

How many times have we seen the request to “set back” or “set forward” a sequence to its original value. Reasons for doing this have been e.g. having a sequence, that start every time period (e.g. month) new or a heavy import has been done and the sequence should now be set to the max-value+1 of the rows inserted to the table.

There have been various ways to solve this kind of issues.

  • To set the sequence back normally we dropped and recreated it (and hopefully did not forget the grants).
  • To set the sequence to a new high value, we just built a loop or changed the stepwidth to get the sequence to the desired value.

All those workarounds are no longer needed with ORACLE 18c as there is a new option that we can use to handle both cases.


Test 1:

After having consumed 500 number from the sequence, we would like to set it back to it’s original value (1).

CREATE SEQUENCE MonthlySeq 
   START WITH 1
   INCREMENT BY 1 
   MINVALUE 1 
   MAXVALUE 9999;

DECLARE 
   l_val NUMBER; 
BEGIN 
   FOR i IN 1..500 
   LOOP 
      l_val := MonthlySeq.NEXTVAL; 
   END LOOP; 
END;
/

SELECT MonthlySeq.nextval 
 FROM dual;

NEXTVAL
-------
    501
 
ALTER SEQUENCE MonthlySeq RESTART;

SELECT MonthlySeq.nextval 
 FROM dual;

NEXTVAL
-------
      1

So this works as expected.


Test 2:

After having consumed 500 number from the sequence, we would like to set it forward to 1500.

CREATE SEQUENCE MonthlySeq 
   START WITH 1
   INCREMENT BY 1 
   MINVALUE 1 
   MAXVALUE 9999;

DECLARE 
   l_val NUMBER; 
BEGIN 
   FOR i IN 1..500 
   LOOP 
      l_val := MonthlySeq.NEXTVAL; 
   END LOOP; 
END;
/

SELECT MonthlySeq.nextval 
 FROM dual;

NEXTVAL
-------
    501
 
ALTER SEQUENCE MonthlySeq RESTART START WITH 1500;

SELECT MonthlySeq.nextval 
 FROM dual;

NEXTVAL
-------
   1500

Excellent….


Test 3:

I expect the ALTER SEQUENCE statement to commit open transactions…but just to be sure.

CREATE SEQUENCE MonthlySeq 
   START WITH 1
   INCREMENT BY 1 
   MINVALUE 1 
   MAXVALUE 9999;

CREATE TABLE t (x NUMBER);

DECLARE 
   l_val NUMBER; 
BEGIN 
   FOR i IN 1..500 
   LOOP 
      l_val := MonthlySeq.NEXTVAL; 
   END LOOP; 
END;
/

SELECT MonthlySeq.nextval 
 FROM dual;

NEXTVAL
-------
    501

INSERT INTO t VALUES (1);
 
ALTER SEQUENCE MonthlySeq RESTART START WITH 1500;

ROLLBACK;

SELECT *
 FROM t;

X
-
1

ok, would have been nice if not, but it’s what was expected.


Conclusion

Still this is a nice feature available in ORACLE 18c but not in the documentation (SQL 

There you will find:

To restart the sequence at a different number, you must drop and re-create it.

and

You will also miss the RESTART option in the syntax diagram of the ALTER SEQUENCE command…