How to Get Sequence Current or Next Value Without Side Effect? How Soon will Sessions Need to Read from SEQ$ Table? 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 are 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. If you ever ask how many values are left in the sequence cache for the session, you can use this SQL to find out. The last column I call CACHELEFT is what you need: SQL> select sequence_name, nextvalue, highwater, highwater-nextvalue cacheleft 2 from v$_sequences where sequence_owner = 'APPUSER' order by highwater; SEQUENCE_NAME NEXTVALUE HIGHWATER CACHELEFT ------------------------------ ---------- ---------- ---------- PERSON_S 9716 9734 18 FILE_INSTANCE_TYPE_SEQUENCE_S 62808 62817 9 STUDY_S 63508 63517 9 SERIES_S 194639 194652 13 BAG_S 260850 260865 15 LOCATION_S 265180 265189 9 Since my cache's are all 20, the cacheleft has a max value of 19. Once the number reaches 20, the session must read from SEQ$ table and cache the next 20 in shared pool.