Oracle PL/SQL

update oracle sequence last number

I only realized this last week that direct update of the last number of the sequence is not allowed in Orable. Or maybe I just don't know how. So, I created a workaround.

ALTER SEQUENCE MY_SEQUENCE_NAME_SQ
INCREMENT BY 300;
SELECT MY_SEQUENCE_NAME_SQ.NEXTVAL FROM DUAL;
ALTER SEQUENCE MY_SEQUENCE_NAME
INCREMENT BY 1;

Explanation

The first SQL modifies increment number to 300. That means the next time I call the NEXTVAL, I'll get the current number + 300. In that way, I can skip some numbers. And after achieving the purpose, I set back the increment to just 1.

This is a unelegant workaround, actually. But it's something that work and I needed it immediately to solve a problem in production wherein the sequence number is no longer in-synch with the actual current max number in the table (due to manual insert/update).