oracle 11.2.0.4 sequence之dba_sequences last_number含義測試之一

測試結論
1，檢視dba_sequences之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.


a,last_number為寫入到磁碟的最後的序列值
b,如果序列使用cache,last_number為儲存在CACHE中最大或最後的序列值
c,last_number的值很可能會大於已使用序列的最後的值




2，可見只要序列的當前值sequence_name.currval不超過last_number，則LAST_NUMBER不會發生變化




測試明細


1，資料庫版本
SQL> select * from v$version where rownum=1;


BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production


2，dba_sequences官方定義
SQL> desc dba_sequences;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SEQUENCE_OWNER                            NOT NULL VARCHAR2(30)
 SEQUENCE_NAME                             NOT NULL VARCHAR2(30)
 MIN_VALUE                                          NUMBER
 MAX_VALUE                                          NUMBER
 INCREMENT_BY                              NOT NULL NUMBER
 CYCLE_FLAG                                         VARCHAR2(1)
 ORDER_FLAG                                         VARCHAR2(1)
 CACHE_SIZE                                NOT NULL NUMBER
 LAST_NUMBER                               NOT NULL NUMBER


3，建立序列
SQL> create sequence seq_zxy;


Sequence created.


4，檢視剛建立的序列資訊，可見last_number為1
SQL> select sequence_owner,sequence_name,min_value,max_value,increment_by,cycle_flag,order_flag,cache_size,last_number from dba_sequences where sequence_owner='SYS'
  2  and sequence_name='SEQ_ZXY';


SEQUENCE_OWNER                 SEQUENCE_NAME                   MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ------------------------------ ---------- ---------- ------------ - - ---------- -----------
SYS                            SEQ_ZXY                                 1 1.0000E+28            1 N N         20           1


5，currval不能直接使用，必須先要nextval之後再使用currval
SQL> select seq_zxy.currval from dual;
select seq_zxy.currval from dual
       *
ERROR at line 1:
ORA-08002: sequence SEQ_ZXY.CURRVAL is not yet defined in this session




SQL> select sequence_owner,sequence_name,min_value,max_value,increment_by,cycle_flag,order_flag,cache_size,last_number from dba_sequences where sequence_owner='SYS'
  2  and sequence_name='SEQ_ZXY';


SEQUENCE_OWNER                 SEQUENCE_NAME                   MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ------------------------------ ---------- ---------- ------------ - - ---------- -----------
SYS                            SEQ_ZXY                                 1 1.0000E+28            1 N N         20           1


SQL> 


SQL> select seq_zxy.nextval from dual;


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




SQL> select sequence_owner,sequence_name,min_value,max_value,increment_by,cycle_flag,order_flag,cache_size,last_number from dba_sequences where sequence_owner='SYS'
  2  and sequence_name='SEQ_ZXY';


SEQUENCE_OWNER                 SEQUENCE_NAME                   MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ------------------------------ ---------- ---------- ------------ - - ---------- -----------
SYS                            SEQ_ZXY                                 1 1.0000E+28            1 N N         20          21


SQL> 


SQL> select seq_zxy.nextval from dual;


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




SQL> select sequence_owner,sequence_name,min_value,max_value,increment_by,cycle_flag,order_flag,cache_size,last_number from dba_sequences where sequence_owner='SYS'
  2  and sequence_name='SEQ_ZXY';


SEQUENCE_OWNER                 SEQUENCE_NAME                   MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ------------------------------ ---------- ---------- ------------ - - ---------- -----------
SYS                            SEQ_ZXY                                 1 1.0000E+28            1 N N         20          21


6,變化sequence cache_size，可知cache size變化後last_number也會變化
SQL> alter sequence seq_zxy cache 200;


Sequence altered.




SQL> select sequence_owner,sequence_name,min_value,max_value,increment_by,cycle_flag,order_flag,cache_size,last_number from dba_sequences where sequence_owner='SYS'
  2  and sequence_name='SEQ_ZXY';


SEQUENCE_OWNER                 SEQUENCE_NAME                   MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ------------------------------ ---------- ---------- ------------ - - ---------- -----------
SYS                            SEQ_ZXY                                 1 1.0000E+28            1 N N        200           3






7，檢視dba_sequences之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.


a,last_number為寫入到磁碟的最後的序列值
b,如果序列使用cache,last_number為儲存在CACHE中最大或最後的序列值
c,last_number的值很可能會大於已使用序列的最後的值




8，可見只要序列的當前值不超過last_number，則LAST_NUMBER不會發生變化
SQL> select seq_zxy.nextval from dual;


   NEXTVAL
----------
         4


SQL> select sequence_owner,sequence_name,min_value,max_value,increment_by,cycle_flag,order_flag,cache_size,last_number from dba_sequences where sequence_owner='SYS'
  2  and sequence_name='SEQ_ZXY';


SEQUENCE_OWNER                 SEQUENCE_NAME                   MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ------------------------------ ---------- ---------- ------------ - - ---------- -----------
SYS                            SEQ_ZXY                                 1 1.0000E+28            1 N N        200         203


SQL> 
SQL> 
SQL> select seq_zxy.nextval from dual;


   NEXTVAL
----------
         5


SQL> select sequence_owner,sequence_name,min_value,max_value,increment_by,cycle_flag,order_flag,cache_size,last_number from dba_sequences where sequence_owner='SYS'
  2  and sequence_name='SEQ_ZXY';


SEQUENCE_OWNER                 SEQUENCE_NAME                   MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ------------------------------ ---------- ---------- ------------ - - ---------- -----------
SYS                            SEQ_ZXY                                 1 1.0000E+28            1 N N        200         203


SQL>