Get oracle sequenve next value without change it

oracle sequence nextval
S.K picture S.K · Jan 13, 2014 · Viewed 7.1k times · Source

I have a big problem. I need to know the next value of an oracle sequence without changing it.

If I use sequence.NEXTVAL , I get what I need. The problem is that the value changed, so the next record will get this value + 1 and it's not good for me.

I know I can use sequence.CURRVAL and it's great because it does not change the value, but in case of no records, it's not working and in this case the sequence value can be any number (and not only 1 cause the sequence value steel exist).

Please help me to find a solution.

Thank you vary much !!!!!

Answer

David Aldridge picture David Aldridge · Jan 13, 2014

You should definitely not rely on never missing a value in a sequence, as they optimise for concurrency over sequential numbering. There are quite a few situations in which a number can be "lost".

Furthermore, the value visible in the dba_sequences may not be the actual next value, as the numbers are assigned from an in-memory cache. The underlying sequence metadata table has no data on the usage of that cache. You should also bear in mind that in a RAC system each instance has its own cache of sequence numbers.

You might describe the problem you are trying to solve, as it could be that sequences are not an appropriate mechanism for you.

Related questions

How do I reset a sequence in Oracle?

In PostgreSQL, I can do something like this: ALTER SEQUENCE serial RESTART WITH 0; Is there an Oracle equivalent?

Read More
How to retrieve the current value of an oracle sequence without increment it?

Is there an SQL instruction to retrieve the value of a sequence that does not increment it. Thanks. EDIT AND CONCLUSION As stated by Justin Cave It's not useful to try to "save" sequence number so select a_seq.nextval …

Read More
How to create an Oracle sequence starting with max value from a table?

Trying to create a sequence in Oracle that starts with the max value from a specific table. Why does this not work? CREATE SEQUENCE transaction_sequence MINVALUE 0 START WITH (SELECT MAX(trans_seq_no) FROM TRANSACTION_LOG) INCREMENT BY 1 CACHE 20;

Read More