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
2start with 1
3increment by 1
4maxvalue 99999
5nocache;
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
2FROM DBA_SEQUENCES
3WHERE 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
2FROM X$KGLOB
3WHERE KGLOBTYD='SEQUENCE'
4 AND KGLNAOWN =UPPER(TRIM('&SEQ_OWNER'))
5ORDER 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> 扫描上面二维码关注我如果你真心觉得文章写得不错，而且对你有所帮助，那就不妨帮忙“推荐"一下，您的“推荐”和”打赏“将是我最大的写作动力！本文版权归作者所有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接.
来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！
页:
[1]