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…


Update (6.1.2020)

After having filed an SR with ORACLE pointing out that the RESTART option is missing in the documentation, I got the following answer:

It (ALTER SEQUENCE … RESTART) was created for internal operations –
Requested to use (ALTER SEQUENCE) as per documentation.

Which means for me, that the restart option is not supported but as oracle is using it internally …