sequence使用

来源：互联网发布：做平面图用什么软件编辑：程序博客网时间：2024/05/17 04:11

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
此时已经断号

0 0