Sequence starts with 1 after reaching MAXVALUE even STARTWITH 100 specified

Question

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?

Solution 1

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.

Solution 2

When your sequence cycles, it starts again at the MINVALUE of the sequence. That defaults to 1 if you don't specify a value. If you wanted the sequence to start again at 100, you'd need to specify a MINVALUE of 100.