Sometimes it is necessary to re-set the value of an Oracle sequence:
DECLARE
PROCEDURE set_sequence_value (p_sequence_owner IN VARCHAR2,
p_sequence_name IN VARCHAR2,
p_new_value IN NUMBER) IS
l_reduce_by NUMBER;
l_last_number NUMBER;
l_seq_val NUMBER;
BEGIN
SELECT last_number
INTO l_last_number
FROM dba_sequences s
WHERE sequence_owner = p_sequence_owner
AND sequence_name = p_sequence_name;
dbms_output.put_line('last number: '||l_last_number);
IF l_last_number != p_new_value
THEN
l_reduce_by := p_new_value - l_last_number;
ELSE
RETURN;
END IF;
EXECUTE IMMEDIATE 'alter sequence '||p_sequence_owner||'.'||p_sequence_name||' increment by '||TO_CHAR(l_reduce_by);
EXECUTE IMMEDIATE 'SELECT '||p_sequence_owner||'.'||p_sequence_name||'.NEXTVAL FROM DUAL' INTO l_seq_val;
EXECUTE IMMEDIATE 'alter sequence '||p_sequence_owner||'.'||p_sequence_name||' increment by 1';
END; --set_sequence_value.
BEGIN
set_sequence_value('YOUR_SCHEMA_NAME_HERE','GB_TEST_SEQ',9);
END;
/