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

SELECT last_number
  FROM all_sequences
 WHERE sequence_owner="<sequence owner>"
   AND sequence_name="<sequence_name>";

You can get a variety of sequence metadata from user_sequences, all_sequences and dba_sequences.

These views work across sessions.

EDIT:

If the sequence is in your default schema then:

SELECT last_number
  FROM user_sequences
 WHERE sequence_name="<sequence_name>";

If you want all the metadata then:

SELECT *
  FROM user_sequences
 WHERE sequence_name="<sequence_name>";

EDIT2:

A long winded way of doing it more reliably if your cache size is not 1 would be:

SELECT increment_by I
  FROM user_sequences
 WHERE sequence_name="SEQ";

      I
-------
      1

SELECT seq.nextval S
  FROM dual;

      S
-------
   1234

-- Set the sequence to decrement by 
-- the same as its original increment
ALTER SEQUENCE seq 
INCREMENT BY -1;

Sequence altered.

SELECT seq.nextval S
  FROM dual;

      S
-------
   1233

-- Reset the sequence to its original increment
ALTER SEQUENCE seq 
INCREMENT BY 1;

Sequence altered.

Just beware that if others are using the sequence during this time – they (or you) may get

ORA-08004: sequence SEQ.NEXTVAL goes below the sequences MINVALUE and cannot be instantiated

Also, you might want to set the cache to NOCACHE prior to the resetting and then back to its original value afterwards to make sure you’ve not cached a lot of values.

More Related Contents:

Leave a Comment Cancel reply