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

Question

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>';

Hope it helps...

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.

select MY_SEQ_NAME.currval from DUAL;

Keep in mind that it only works if you ran select MY_SEQ_NAME.nextval from DUAL; in the current sessions.

The follows is often used:

select field_SQ.nextval from dual;  -- it will increase the value by 1 for each run
select field_SQ.currval from DUAL;

However the following is able to change the sequence to what you expected. The 1 can be an integer (negative or positive)

alter sequence field_SQ increment by 1 minvalue 0

This is not an answer, really and I would have entered it as a comment had the question not been locked. This answers the question:

Why would you want it?

Assume you have a table with the sequence as the primary key and the sequence is generated by an insert trigger. If you wanted to have the sequence available for subsequent updates to the record, you need to have a way to extract that value.

In order to make sure you get the right one, you might want to wrap the INSERT and RonK's query in a transaction.

RonK's Query:

select MY_SEQ_NAME.currval from DUAL;

In the above scenario, RonK's caveat does not apply since the insert and update would happen in the same session.