关于Oracle 数据库中的sequence

最新推荐文章于 2022-10-14 10:31:39 发布

msdnchina

最新推荐文章于 2022-10-14 10:31:39 发布

阅读量1.4k

点赞数

分类专栏： Oracle DB 文章标签： oracle sequence dba_sequences

本文链接：https://blog.csdn.net/msdnchina/article/details/51286833

版权

Oracle DB 专栏收录该内容

773 篇文章 3 订阅

订阅专栏

dba_sequences.last_number:
无cache时，取一次nextval，last_number递增一次
有cache时, 若在cache之内的取一次nextval，last_number不变
若在cache之外的取一次nextval, last_number=last_number+cache值

dba_sequences.last_number未必是该sequence的下一个值，这其中一个原因是该sequence有cache

若是该sequence没有cache，那么dba_sequences.last_number是该sequence的下一个值

F:\oracle\product\10.2.0\db_1\BIN>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Sat Apr 30 16:01:14 2016

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

16:01:15 sys@FS> create sequence scott.seq_tmp_1 start with 1 increment by 1 max
value 99999999 nocycle cache 10 order;

Sequence created.

Elapsed: 00:00:00.00
16:01:17 sys@FS> select SEQUENCE_NAME,LAST_NUMBER from dba_sequences where seque
nce_name='SEQ_TMP_1';

SEQUENCE_NAME                  LAST_NUMBER
------------------------------ -----------
SEQ_TMP_1                                1

Elapsed: 00:00:00.00
16:02:15 sys@FS> select scott.seq_tmp_1.currval from dual;
select scott.seq_tmp_1.currval from dual
             *
ERROR at line 1:
ORA-08002: sequence SEQ_TMP_1.CURRVAL is not yet defined in this session


Elapsed: 00:00:00.02
16:02:21 sys@FS> select scott.seq_tmp_1.nextval from dual;

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

Elapsed: 00:00:00.00
16:02:52 sys@FS> select scott.seq_tmp_1.currval from dual;

   CURRVAL
----------
         1

Elapsed: 00:00:00.00
16:02:56 sys@FS>
16:02:57 sys@FS>
16:02:58 sys@FS>
16:02:58 sys@FS>
16:02:58 sys@FS>
16:02:58 sys@FS> select SEQUENCE_NAME,LAST_NUMBER from dba_sequences where seque
nce_name='SEQ_TMP_1';

SEQUENCE_NAME                  LAST_NUMBER
------------------------------ -----------
SEQ_TMP_1                               11

Elapsed: 00:00:00.01
16:03:00 sys@FS> select scott.seq_tmp_1.nextval from dual;

   NEXTVAL
----------
         2

Elapsed: 00:00:00.00
16:03:08 sys@FS> select scott.seq_tmp_1.currval from dual;

   CURRVAL
----------
         2

Elapsed: 00:00:00.00
16:03:11 sys@FS> select SEQUENCE_NAME,LAST_NUMBER from dba_sequences where seque
nce_name='SEQ_TMP_1';

SEQUENCE_NAME                  LAST_NUMBER
------------------------------ -----------
SEQ_TMP_1                               11

Elapsed: 00:00:00.00
16:03:26 sys@FS>