How to reset current value for sequence

This is one interesting issue regarding altering a sequence to reset CURRENT VALUE.
Because this current value is not specified in CREATE SEQUENCE statement, it can not be reset with ALTER SEQUENCE statement. Instead you have the following methods:

1. Drop and recreate the sequence. Take care of the object affected by this sequence (triggers, procedures, etc.), because they will be invalidated.

2. Use ALTER SEQUENCE command to change some sequence limits and the generate new values

- review your sequence DDL

select 'create sequence '||sequence_name||
       ' increment by '||increment_by||
       ' start with '||last_number||
       ' maxvalue '||max_value||
       decode(cycle_flag,'N',' NOCYCLE ',' CYCLE ')||
       decode(cache_size,0,'NOCACHE ','CACHE '||cache_size)
 from user_sequences where sequence_name = 'SEQ_TEST';


- view current value for this sequence
select seq_test.currval from dual;
5

- change the maximum value of the sequence equal to current value and make the sequence to cycle if maxvalue is reach
alter sequence seq_test maxvalue 5 cycle nocache;


- generate new value for the sequence
select seq_test.nextval from dual;

- check that the new value is 0

select seq_test.currval from dual;
0

- use ALTER statement to change the sequence parameters like they use to be

alter sequence seq_test nomaxvalue nocycle cache 20;