Oracle Sequence by example.

===========================================

Reset value of a sequence. 

===========================================

A. See current value of a sequence 

SELECT LAST_NUMBER 

FROM ALL_SEQUENCES 

WHERE SEQUENCE_OWNER = '<SEQUENCE OWNER>' 

  AND SEQUENCE_NAME = '<SEQUENCE_NAME>'; 

B. Check data in table is in sync with sequence values.

SELECT MAX(seq_column_id) FROM my_seq_table 

--for example: 180

SELECT COUNT(*) FROM my_seq_table

 --180 SELECT 

SELECT LAST_NUMBER FROM ALL_SEQUENCES 

 WHERE SEQUENCE_NAME = 'MY_SEQUENCE_SEQ'

 --19

C. Set value of a sequence to the desired value. 

In this example, increment from 19 to 180.

ALTER SEQUENCE MY_SEQUENCE_SEQ INCREMENT BY 160;

SELECT MY_SEQUENCE_SEQ.NEXTVAL FROM DUAL; 

ALTER SEQUENCE MY_SEQUENCE_SEQ INCREMENT BY 1;

SELECT MY_SEQUENCE_SEQ.NEXTVAL FROM DUAL;   

SELECT LAST_NUMBER FROM ALL_SEQUENCES 

 WHERE SEQUENCE_NAME = 'MY_SEQUENCE_SEQ'

--now it is 180

Next value from sequence would be 181.