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 …