In Oracle, sequences are generally used to auto generate column IDs. Re-setting might be needed when someone sets the increment value wrongly or it appears this sequence has not always been used to insert new values into the table and you need to correct it.
In a particular project of mine, I have a sequence used to seed my (Integer based) primary keys in an Oracle table. I need to use the sequence and then have to revert back to old value. In SQL server TSQL reseed identity is nice and easy, but with Oracle PLSQL, it's not that straight forward, so after doing some R&D, I have come up with this solution:
select job_seq.nextval from dual;
ALTER SEQUENCE job_seq increment by -1 minvalue 0;
select job_seq.nextval from dual;
ALTER SEQUENCE job_seq increment by 1;
job_seq is the particular sequence which needed to be reset. What the above code does is retrieve the next sequence value then alter the sequence by -1 so that next time sequence next value is called it increments by -1, and after that we alter the sequence to the original declaration. If current sequence number of job_seq is 112, then the above code will yield:
113
ALTER SEQUENCE job_seq succeeded.
112
ALTER SEQUENCE job_seq succeeded.
