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 …
I’m using 12.1 and the RESTART option worked as well. I hope Oracle documents it soon.
Pingback: Basic SQL: All about sequences – svenweller
Pingback: SQL Quickie: How to reset an identity column – svenweller