CODEWITHSUNDEEP
sqloracle-databasesequence
Vik
Vik

Reputation: 9299

how to find the start with value of a db sequence

I was trying to find a way to know the start with value of given sequence in oracle sql. Is there a way to do that?

Further, I wanted to list that with the below for the current max value as 

select object_name.NEXTVAL 
from DUAL 
where object_name IN 
(select object_name from all_objects where object_type like 'SEQUENCE')

but this results into syntax error.

Upvotes: 1

Views: 1068

Answers (1)

Maheswaran Ravisankar
Maheswaran Ravisankar

Reputation: 17920

select sequence_name,
       LAST_NUMBER as NEXT_VAL,
       MIN_VALUE as START_WITH
from dba_sequences 
where sequence_name ='S';

P.S.
By the time of the result, chances are likely that the sequence could have been increment by another process

LAST_NUMBER will not be accurate if your sequence is defined with a CACHE > 1 ! (sequences would be generated in advance and cached for performance) So only NEXT_VAL can be used to find it. But calling NEXT_VAL even for printing purpose would increment it.

MIN_VALUE will be equal to START_WITH only if START_WITH is not specified while creation! And There's NO way to find it, except for checking the DDL.

Query to get the DDL of Sequence:

dbms_metadata.get_ddl('SEQUENCE', 'SEQ_NAME')

Credits to Nicholas Krasnov

Upvotes: 2

Related Questions