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>
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.
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
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
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