Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Fabian.

Asked: December 24, 2011 - 1:39 pm UTC

Last updated: April 24, 2019 - 1:58 pm UTC

Version: 11.2.2

Viewed 100K+ times! This question is

You Asked

Hi. Simple question :-)
Is it possible to check current value of sequence? I though it is stored in SEQ$ but that is not true (at least in 11g). So is it now possible at all?
Regards

and we said...

you can check the current value of a sequence in your session by using "currval"


sequence.currval

will return that. IF you mean "the current value any session would see at that point in time" - you would have to call "sequence.nextval".


Yes, that would "waste" a sequence value - but so what. Even if you did that 100,000 times per second:

ops$tkyte%ORA11GR2> select rpad( '9',28, '9' ) / 100000 / 60 / 60 / 24 / 366 from dual;

RPAD('9',28,'9')/100000/60/60/24/366
------------------------------------
                          3.1623E+15


it would take that many years before you exhausted the sequence.


If you look in seq$ (yes, it is there) or better yet, in dba_sequences, it will always overestimate it:

ops$tkyte%ORA11GR2> select sequence_name, last_number from dba_sequences where sequence_owner = user;

SEQUENCE_NAME                  LAST_NUMBER
------------------------------ -----------
S                                        1

ops$tkyte%ORA11GR2> select s.nextval from dual;

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

ops$tkyte%ORA11GR2> select sequence_name, last_number from dba_sequences where sequence_owner = user;

SEQUENCE_NAME                  LAST_NUMBER
------------------------------ -----------
S                                       21

ops$tkyte%ORA11GR2> alter sequence s cache 100
  2  /

Sequence altered.

ops$tkyte%ORA11GR2> select s.nextval from dual;

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

ops$tkyte%ORA11GR2> select sequence_name, last_number from dba_sequences where sequence_owner = user;

SEQUENCE_NAME                  LAST_NUMBER
------------------------------ -----------
S                                      102




by up to the cache size for the sequence.

Rating

  (6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Current Value of Sequence

Rajeshwaran Jeyabal, December 24, 2011 - 4:41 pm UTC

Tom:
If you look in seq$ (yes, it is there)
I don't see the current value in sys.seq$. It's very simillar to dba_sequence.
rajesh@ORA11GR2> create sequence s;

Sequence created.

Elapsed: 00:00:00.26
rajesh@ORA11GR2> select s.nextval from dual;

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

Elapsed: 00:00:00.14
rajesh@ORA11GR2>
rajesh@ORA11GR2> select *
  2  from dba_sequences
  3  where sequence_name ='S'
  4  /

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

Elapsed: 00:00:00.10
rajesh@ORA11GR2>
rajesh@ORA11GR2> select * from sys.seq$
  2  where obj# = (select object_id from dba_objects where object_name ='S')
  3  /

      OBJ# INCREMENT$   MINVALUE   MAXVALUE     CYCLE#     ORDER$      CACHE  HIGHWATER AUDIT$       FLAGS
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
   6350963          1          1 1.0000E+28          0          0         20         21 ----------       8

Elapsed: 00:00:00.10
rajesh@ORA11GR2>

Tom Kyte
December 24, 2011 - 8:33 pm UTC

it is the current value of the sequence on disk - the same as dba-sequences - I just explained what it is and how it varies from the nextval value above??????

thank you

Fabian, December 25, 2011 - 4:46 am UTC

Well, I was not enough precise when asking -- my apologise.
I wanted to get last used value of sequence in unit tests. So performance has no meaning. I do not want to use currval because I'm not always in the same session. So that was the purpose of question.
For now I used to make sequence nocache and then query watermark from SEQ$ (of course only on dev db) so probably stay with that.
Thank you anyway.
Tom Kyte
December 25, 2011 - 11:04 am UTC

the only way to get the last sequence from a series of unit tests would be to query currval in that session - nothing else would tell you what the last sequence in some arbitrary session was.

For now I used to make sequence nocache and then query watermark from SEQ$ (of
course only on dev db) so probably stay with that.


that is a horrifically bad idea, why would you do that? Why would you not just change your unit tests to report out this information you seem to find to be vital? Setting a sequence nocache is a really bad (performance wise) idea. And unless you run that way in production (really really bad) why would you test that way? Part of the goal of a test is to simulate real life as much as possible.

Fabian, December 26, 2011 - 1:29 pm UTC

sure, you are right, that is bad idea, that's why I was looking for the other one. Now I see that there is no other than NOCACHE (sure, even on dev it is ugly) and the most natural currval (and preffered whenever possible).
Regards,

rpad on sequence.nextval where output length remains constants

Kuldeep Sharma, January 13, 2012 - 7:42 am UTC

Hi,
Sequence Name: Seq1(Min value=1, Start with= 1)
Iwant my output as the sequence.nextval + 0 on right side but the output length should must be eight.

Query: select rpad('00000000',8,Seq1.nextval);
it gives me otput for value one to nine correctly but after nine it trunc the last dight of nextval i want the next val

Tom Kyte
January 17, 2012 - 9:29 am UTC

select to_char( seq1.nextval, 'fm00000000' ) from dual;



ops$tkyte%ORA11GR2> create sequence s;

Sequence created.

ops$tkyte%ORA11GR2> select to_char( s.nextval, 'fm00000000' ) from all_users;

TO_CHAR(S
---------
00000001
00000002
00000003
00000004
00000005
00000006
00000007
00000008
00000009
00000010
00000011
00000012
00000013
00000014
00000015
00000016
00000017
00000018
00000019
00000020
00000021
00000022
00000023

A reader, April 27, 2017 - 12:23 pm UTC

by using currval we get the current value of a sequence

Sequence value decreased instead of increment.

Praveen Darla, April 24, 2019 - 12:00 pm UTC

Hi Tom,
My sequence curr value is now 570 and max value of that column is 7430. Sequence value is decreased to 570 and sequence start with value is 640, I dont know how it is working . Now it is throwing unique constraint violated . Please assist with a solution.

Please find the sequence query
------------------------------------
CREATE SEQUENCE s1 MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 640 CACHE 20 NOORDER NOCYCLE NOPARTITION ;

Thanks and Regards,
Praveen Darla
Chris Saxon
April 24, 2019 - 1:58 pm UTC

If you're using DBMS_metadata to get the DDL - which is what many tools use under-the-hood - then the START WITH value will increase as you fetch more values from the sequence:

create sequence s1 
  increment by 1 start with 95 cache 5;
  
select s1.nextval from dual
connect by level <= 10;

NEXTVAL   
        95 
        96 
        97 
        98 
        99 
       100 
       101 
       102 
       103 
       104

set long 10000
select dbms_metadata.get_ddl ( 'SEQUENCE', 'S1' )
from   dual;

DBMS_METADATA.GET_DDL('SEQUENCE','S1')                                                                                                                                  

   CREATE SEQUENCE  "CHRIS"."S1"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 105 CACHE 5 NOORDER  NOCYCLE  NOKEEP  NOSCALE  GLOBAL ;   


Note START WITH is now 105. Even though I created the sequence using START WITH 95.

So you're almost certainly seeing the effect of normal sequence use - unless someone fiddled with its definition!

If this is clashing with existing values, bump the current value:

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9532125500346817364