How to Get Sequence Current or Next Value Without Side Effect? 

If you SELECT S.NEXTVAL FROM DUAL, where S is a sequence, you get the next 
value of the sequence but its next value is incremented by INCREMENT_BY because 
of this query. If you SELECT S.CURRVAL FROM DUAL, it throws the error ORA-8002 
unless you already selected its NEXTVAL before in the current session. So, how 
do you get the "current" or next value of the sequence without having already 
used it in your session and without incrementing it? 

Method 1 (I think I got this from a message posted by Steve Adams; but errors 
mine): 

ALTER SEQUENCE S NOCACHE;
--Get the next value
SELECT LAST_NUMBER FROM USER_SEQUENCES WHERE SEQUENCE_NAME = 'S';
ALTER SEQUENCE S CACHE 20; --assuming 20 is the original cache size 

This is easy and quick. Can be used on systems where there's no very active 
sessions that frequently select the next value of the sequence. 

Method 2: 

You can use SYS.V$_SEQUENCES, one of the few views owned by SYS whose names 
start with v$_ instead of v_$. 

If S has not been used even once since instance startup or last shared pool 
flushing, S won't be in V$_SEQUENCES. It will show up in there after any 
session merely mentions it, even if the SQL fails parsing (in this case, the 
parse failure has to point to this sequence); for instance, SELECT S.CURRVAL 
FROM DUAL in any session throws ORA-8002 but this SQL "inserts" some info about 
S into V$_SEQUENCES. Unfortunately, the NEXTVALUE column of this view is still 
empty. So that hasn't solved our problem. 

What solves the problem is that if one session, i.e. anybody's session,  ever 
selected the NEXTVAL of S since instance startup or last shared pool flushing, 
V$_SEQUENCES.NEXTVALUE will have the next value of the sequence. Selecting this 
NEXTVALUE column does not increment the sequence value and predicts the next 
value for any session that subsequently mentions S.NEXTVAL. Since you don't 
alter S to NOCACHE, you're unlikely to have even a short duration of 
sequence-related waits and locks. 

The sequence "current" value, however, is not instance wide, but instead 
session-specific (thanks to Lex de Haan for clarification). NEXTVALUE minus 
INCREMENT_BY in V$_SEQUENCES is the current value for only the session that 
most recently selected the sequence's next value.

Most of the time when you want to know the next value of a sequence, it has 
already been used by somebody calling S.NEXTVAL. So it's not hard to meet the 
requirement that the next value of a sequence must have been used by any 
session. Just remember flushing the shared pool cleans out all entries in 
V$_SEQUENCES (which is really the same as flushing the cached sequence 
numbers), increases the LAST_NUMBER of DBA/ALL/USER_SEQUENCES to the maximum 
value last cached and causes sequence gaps. Also remember that you can't let a 
user select on SYS.V$_SEQUENCES by granting SELECT ANY TABLE or SELECT ON 
V$_SEQUENCES privilege to that user; you either have to be SYS or create a view 
as SELECT * FROM V$_SEQUENCES and create a public synonym for the view.