Oracle如何查询SEQUENCE的当前值而不增加SEQUENCE的值

在Oracle数据库中,如何查询一个序列(SEQUENCE)的当前值呢? 我们知道,如果当前会话直接查询序列的CRURRVAL,而不先查询序列的NEXTVAL就会报错:"ORA-08002: sequence SEQ_TEST.CURRVAL is not yet defined in this session". 如下所示:

SQL> create sequence kerry.seq_test
  2  start with 1
  3  increment by 1
  4  maxvalue 99999
  5  nocache;
  
Sequence created.

SQL> 

SQL> select kerry.seq_test.nextval from dual;

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

SQL> select kerry.seq_test.currval from dual;

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

如果退出当前会话,我们重新登录,模拟实际环境,DBA想查询序列(SEQUENCE)的的当前值,如果用这种方法,必须先查询序列的NEXTVAL后才能获取序列的CURRVAL

SQL> select kerry.seq_test.currval from dual;
select kerry.seq_test.currval from dual
*
ERROR at line 1:
ORA-08002: sequence SEQ_TEST.CURRVAL is not yet defined in this session

SQL> select kerry.seq_test.nextval, kerry.seq_test.currval from dual;

   NEXTVAL    CURRVAL
---------- ----------
         2          2

SQL> 

此时, 很多人可能想到通过系统视图DBA_SEQUENCES的LAST_NUMBER查询.

SQL> SET LINESIZE 255
SQL> COL SEQUENCE_OWNER FOR A16;
SQL> COL SEQUENCE_NAME FOR A30;
SQL> COL MAX_VALUE FOR 9999999999999999999999999999999999;
SQL> SELECT SEQUENCE_OWNER, SEQUENCE_NAME,MAX_VALUE, LAST_NUMBER  
  2  FROM DBA_SEQUENCES
  3  WHERE SEQUENCE_NAME=UPPER(TRIM('&SEQUENCE_NAME'));
Enter value for sequence_name: SEQ_TEST
old   3: WHERE SEQUENCE_NAME=UPPER(TRIM('&SEQUENCE_NAME'))
new   3: WHERE SEQUENCE_NAME=UPPER(TRIM('SEQ_TEST'))

SEQUENCE_OWNER   SEQUENCE_NAME                                            MAX_VALUE LAST_NUMBER
---------------- ------------------------------ ----------------------------------- -----------
KERRY            SEQ_TEST                                                     99999           3

SQL> 

此时LAST_NUMBER的值减去1就是序列的当前值. 那么这种方法可行吗? 答案是对于NOCACHE属性的序列, 它是准确的.但是一旦序列
设置了CACHE的话,那么它的值就不准确了.因为DBA_SEQUENCES中的LAST_NUMBER是缓存段的边界值，不是真正的“当前已分配值”，更
不能代替序列的CURRVAL。它主要用于DBA监控，不能用来做业务计算。

那么有没有一种方法比较靠谱的方法查询序列的当前值而不增加序列的当前值呢. 还真有这么一种方法.我们可以从X$KGLOB中获取
序列(SEQUENCE)的当前值.

查看某个具体OWNER下面所有序列的当前值

SET LINESIZE 250;
COL KGLNAOWN FOR A16
COL KGLNAOBJ FOR A30
SELECT KGLNAOWN, KGLNAOBJ, KGLOBTN0-KGLOBTN1 AS SEQ_CURRVAL 
FROM X$KGLOB
WHERE KGLOBTYD='SEQUENCE' 
  AND KGLNAOWN =UPPER(TRIM('&SEQ_OWNER'))
ORDER BY 1;

查看某个具体序列的当前值

SET LINESIZE 250;
COL KGLNAOWN FOR A16
COL KGLNAOBJ FOR A30
SELECT KGLNAOWN, KGLNAOBJ, KGLOBTN0-KGLOBTN1 AS SEQ_CURRVAL 
FROM X$KGLOB
WHERE KGLOBTYD='SEQUENCE' 
  AND KGLNAOWN =UPPER(TRIM('&SEQ_OWNER'))
  AND KGLNAOBJ =UPPER(TRIM('&SEQ_NAME'))
ORDER BY 1;

如下所示:

SQL> SET LINESIZE 250;
SQL> COL KGLNAOWN FOR A16
SQL> COL KGLNAOBJ FOR A30
SQL> SELECT KGLNAOWN, KGLNAOBJ, KGLOBTN0-KGLOBTN1 AS SEQ_CURRVAL 
  2  FROM X$KGLOB
  3  WHERE KGLOBTYD='SEQUENCE' 
  4    AND KGLNAOWN =UPPER(TRIM('&SEQ_OWNER'))
  5  ORDER BY 1;
Enter value for seq_owner: kerry
old   4:   AND KGLNAOWN =UPPER(TRIM('&SEQ_OWNER'))
new   4:   AND KGLNAOWN =UPPER(TRIM('kerry'))

KGLNAOWN         KGLNAOBJ                       SEQ_CURRVAL
---------------- ------------------------------ -----------
KERRY            SEQ_TEST                                 3

SQL>