Consider a sequence named "MY_SEQ" which has been created to generate automatic numbers. Somebody, by mistake, has tried to access the sequence from SQL*Plus and consequently the value has been increased to e.g. 32. Our objective is now to reset the value to 10 so that next created number will have a value of 11.

• Find out the INCREMENT BY parameter for the sequence with the following query

SELECT increment_by
FROM user_sequences
WHERE sequence_name = 'MY_SEQ' ;

This will return 1. It is not always necessary to run the above query to find out the INCREMENT BY value. You can find it out by looking at the column data for which the sequence is used.

• Alter the sequence with a negative INCREMENT BY value to count backwards

ALTER SEQUENCE my_seq INCREMENT BY -1;

• Run the PL/SQL block below to reset the sequence value to a desired number:

DECLARE
temp NUMBER(10);
BEGIN
WHILE(TRUE)
LOOP
SELECT MY_SEQ.NEXTVAL
INTO temp
FROM DUAL;
IF (temp = 10) THEN
EXIT;
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;

• Again reset the INCREMENT BY value to original

ALTER SEQUENCE my_seq INCREMENT BY 1;