Click here to Skip to main content
13,510,146 members
Click here to Skip to main content
Add your own
alternative version

Tagged as


2 bookmarked
Posted 24 Dec 2010

Reset PLSQL sequence value

Rate this:
Please Sign up or sign in to vote.
Reset PLSQL sequence value
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:

ALTER SEQUENCE job_seq succeeded.
ALTER SEQUENCE job_seq succeeded.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Shahriar Iqbal Chowdhury/Galib
Chief Technology Officer
Bangladesh Bangladesh
I am a Software Engineer and Microsoft .NET technology enthusiast. Professionally I worked on several business domains and on diverse platforms. I love to learn and share new .net technology and my experience I gather in my engineering career. You can find me from here

Personal Site
Personal Blog
FB MS enthusiasts group
About Me

You may also be interested in...

Comments and Discussions

-- There are no messages in this forum --
Permalink | Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.180417.1 | Last Updated 24 Dec 2010
Article Copyright 2010 by Shahriar Iqbal Chowdhury/Galib
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid