SQL> create sequence ste_seq
2 start with 1
3 increment by 1
4 nocache
5 noorder;
Sequence created.
SQL> select ste_seq.nextval from dual;
NEXTVAL
----------
1
SQL> drop sequence ste_seq;
Sequence dropped.
Now, this is a normal sequence creation, use and drop. Now you sure expect that a new sequence creation and insertion in a table will start again from 1. Now see below:
SQL> create sequence ste_seq start with 1 increment by 1 nocache noorder ;
Sequence created.
SQL> create table x(x number);
Table created.
SQL> insert into x(x) values(ste_seq.nextval) ;
1 row created.
SQL> select * from x;
X
----------
2
You see that sequence is starting from 2. Going to metalink....note 1050901.3 SEQUENCES NOT STARING WITH '1' WHEN USED IN INSERT ... good. Two published bugs closed as 'NOT A BUG'.
All is related to a feature called deferred segment creationg that is document in metalink note: 887962.1.
This feature permits you to defer the first segment creation when you create an empty table. Also helps to save space that sometimes may be wasted creating empty tables.
Better works when your developers create a table with INITIAL clause oversized that cause, in normal condition, oracle to allocate the first big extent for an empty table.
If you hit this bug and you can't go ahead you can workaround setting
SQL> show parameter def
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation boolean FALSE