玉面飞龙的BLOG

keep a note here.

SQL> create sequence s start with 2 maxvalue 10 cycle nocache minvalue 3;

create sequence s start with 2 maxvalue 10 cycle nocache minvalue 3
*
ERROR at line 1:
ORA-04006: START WITH cannot be less than MINVALUE

SQL> create sequence s start with 3 maxvalue 10 cycle nocache minvalue 3;
Sequence created.

SQL> select s.nextval from dual;
NEXTVAL
———-
3

SQL> select s.nextval from dual;
NEXTVAL
———-
4

…

SQL> select s.nextval from dual;
NEXTVAL
———-
10

SQL> select s.nextval from dual;
NEXTVAL
———-
3 <– minvalue, not “start with” value

SQL> create sequence ss start with 3 maxvalue 10 cycle;
create sequence ss start with 3 maxvalue 10 cycle
*
ERROR at line 1:
ORA-04013: number to CACHE must be less than one cycle

SQL> create sequence ss start with 3 maxvalue 10 cycle cache 1;
create sequence ss start with 3 maxvalue 10 cycle cache 1
*
ERROR at line 1:
ORA-04010: the number of values to CACHE must be greater than 1

SQL> create sequence ss start with 3 maxvalue 10 cycle cache 2;
Sequence created.

SQL> select ss.nextval from dual;
NEXTVAL
———-
3

SQL> select ss.nextval from dual;
NEXTVAL
———-
4

…

SQL> select ss.nextval from dual;

NEXTVAL
———-
10

SQL> select ss.nextval from dual;

NEXTVAL
———-
1 <– minvalue, which is default

SQL> select * from user_sequences where sequence_name = ‘SS’;
SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER

—————————— ———- ———- ———— - - ———- ———–

SS 1 10 1 Y N 2 3

Yong Huang