在Oracle數據庫中,如何查詢一個序列(SEQUENCE)的當前值呢? 我們知道,如果當前會話直接查詢序列的CURRVAL,而不先查詢序列的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>