SQL> create sequence seq1
minvalue 1
maxvalue 999999999999999999999999999
start with 0
increment by 1
cache 20; 2 3 4 5 6
create sequence seq1
*
ERROR at line 1:
ORA-04006: START WITH cannot be less than MINVALUE
提示 起始值不能小于最小值
SQL> create sequence seq1
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
cache 20; 2 3 4 5 6
Sequence created.
SQL> select sequence_name,cache_size,last_number from dba_sequences where sequence_name='SEQ1';
SEQUENCE_NAME CACHE_SIZE LAST_NUMBER
------------------------------ ---------- -----------
SEQ1 20 1
SQL> select seq1.nextval from dual;
NEXTVAL
----------
1
SQL> /
NEXTVAL
----------
2
SQL> select sequence_name,cache_size,last_number from dba_sequences where sequence_name='SEQ1';
SEQUENCE_NAME CACHE_SIZE LAST_NUMBER
------------------------------ ---------- -----------
SEQ1 20 21
SQL> select seq1.nextval from dual;
NEXTVAL
----------
3
SQL> select sequence_name,cache_size,last_number from dba_sequences where sequence_name='SEQ1';
SEQUENCE_NAME CACHE_SIZE LAST_NUMBER
------------------------------ ---------- -----------
SEQ1 20 21
一次分配20到shared pool中
last_number:
Last sequence number written to disk. If a sequence uses caching, the number written to disk is the last number placed in the sequence cache. This number is
likely to be greater than the last sequence number that was used.
修改cahce大小对sequence的影响呢?
SQL> alter sequence seq1 cache 1000;
Sequence altered.
SQL> select sequence_name,cache_size,last_number from dba_sequences where sequence_name='SEQ1';
SEQUENCE_NAME CACHE_SIZE LAST_NUMBER
------------------------------ ---------- -----------
SEQ1 1000 4
SQL> select seq1.nextval from dual;
NEXTVAL
----------
4
SQL> select sequence_name,cache_size,last_number from dba_sequences where sequence_name='SEQ1';
SEQUENCE_NAME CACHE_SIZE LAST_NUMBER
------------------------------ ---------- -----------
SEQ1 1000 1004
那么使用cache是否会导致断号呢？
重启数据库后:
SQL> select seq1.nextval from dual;
NEXTVAL
----------
5
SQL> select sequence_name,cache_size,last_number from dba_sequences where sequence_name='SEQ1';
SEQUENCE_NAME CACHE_SIZE LAST_NUMBER
------------------------------ ---------- -----------
SEQ1 1000 1005
居然没有断号,再次重启数据库
SQL> select seq1.nextval from dual;
NEXTVAL
----------
1005
SQL> select sequence_name,cache_size,last_number from dba_sequences where sequence_name='SEQ1';
SEQUENCE_NAME CACHE_SIZE LAST_NUMBER
------------------------------ ---------- -----------
SEQ1 1000 2005
此时已经断号