Sequence starts with 1 after reaching MAXVALUE even STARTWITH 100 specified

oracle sequence cycle
Mikayil Abdullayev picture Mikayil Abdullayev · Sep 25, 2014 · Viewed 9.4k times · Source

I've come across a very weird Oracle sequence behavior. I have the following Sequence:

CREATE SEQUENCE SEQ1 INCREMENT BY 10 START WITH 100 MAXVALUE 200 CYCLE NOCACHE;

Here's an excerpt from "OCA/OCP Oracle Database 11g All-in-One Exam Guide":

CYCLE Controls the behavior on reaching MAXVALUE or MINVALUE. The default behavior is to give an error, but if CYCLE is specified the sequence will return to its starting point and repeat.

From this I infer that after reaching the MAXVALUE of 200, I'll get 100, as the starting point is 100. But surprisingly I get one. Why is that?

Answer

yamny picture yamny · Sep 25, 2014

Let's look at the following excerpt from document:

Specify CYCLE to indicate that the sequence continues to generate values after reaching either its maximum or minimum value. After an ascending sequence reaches its maximum value, it generates its minimum value. After a descending sequence reaches its minimum, it generates its maximum value.

It means that START WITH value is not enough in your case, so both MINVALUE and MAXVALUE should be settled. Without given MINVALUE, cycle will start from number 1.

Related questions

How do I reset a sequence in Oracle?

In PostgreSQL, I can do something like this: ALTER SEQUENCE serial RESTART WITH 0; Is there an Oracle equivalent?

Read More
How to retrieve the current value of an oracle sequence without increment it?

Is there an SQL instruction to retrieve the value of a sequence that does not increment it. Thanks. EDIT AND CONCLUSION As stated by Justin Cave It's not useful to try to "save" sequence number so select a_seq.nextval …

Read More
How to create an Oracle sequence starting with max value from a table?

Trying to create a sequence in Oracle that starts with the max value from a specific table. Why does this not work? CREATE SEQUENCE transaction_sequence MINVALUE 0 START WITH (SELECT MAX(trans_seq_no) FROM TRANSACTION_LOG) INCREMENT BY 1 CACHE 20;

Read More