Oracle Sequence starting with 2 instead of 1

Question

Unexpected behavior:

I am encountering strange behavior of Oracle sequences with 11g (works with 10g):

CREATE SEQUENCE test_sequence START WITH 1;
CREATE TABLE test_table ( val INT );

INSERT INTO test_table VALUES ( test_sequence.NEXTVAL );

Even though the sequence starts with 1, the first value inserted is 2:

SELECT * FROM test_table;

       VAL
----------
         2

---

Expected behavior:

Selecting NEXTVAL without the insert works as expected:

CREATE SEQUENCE test_sequence_2 START WITH 1;

SELECT test_sequence_2.NEXTVAL FROM dual

   NEXTVAL
----------
         1

---

Question:

Can anyone reproduce this using Oracle 11g? Is this a known issue?

I'm using
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production.

Answer 1

Use:

CREATE SEQUENCE SQ_SEQUENCE_NAME
    INCREMENT BY 1
    START WITH 1
    MINVALUE 0  -- This will ensure start at 1!
    MAXVALUE 99
    NOCYCLE
    NOCACHE
    ORDER;