Reset Oracle Sequence

Posted: December 16, 2009 in Oracle, PL/SQL

While on one of my projects in recent time I faced a situation where we needed to reset the value of the sequence of Oracle. Following are steps I followed to do the same.

1) Suppose you create a SEQUENCE like shown below:

CREATE SEQUENCE TESTSEQ
INCREMENT BY 1
MINVALUE 1
MAXVALUE 500
NOCACHE
NOCYCLE
NOORDER

2) Now you fetch values from SEQUENCE. Lets say I have fetched four times as shown below.

SELECT TESTSEQ.NEXTVAL FROM dual
SELECT TESTSEQ.NEXTVAL FROM dual
SELECT TESTSEQ.NEXTVAL FROM dual
SELECT TESTSEQ.NEXTVAL FROM dual

3) After executing above four commands the value of the SEQUENCE will be 4. Now suppose I have to reset the value of the SEQUENCE to 1 again. The follow the

following steps. Follow all the steps in the same order as shown below:

 a) ALTER SEQUENCE TESTSEQ INCREMENT BY -3;
 b) SELECT TESTSEQ.NEXTVAL FROM dual
 c) ALTER SEQUENCE TESTSEQ INCREMENT BY 1;
 d) SELECT TESTSEQ.NEXTVAL FROM dual

and voilla you’ve done it!!! Happy learning and keep posting all your nitty oracle tips to help others learn….

All your comments and suggestions are most welcome.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s