How do you refresh a sequence?

[dba_admin]

How do you refresh (reset) a sequence so that it start from the beginning?

[pando]

you have to change your increment interval to negative then sleect the nextval from dual, this will decrease your sequence number, after that change the increment again to the normal interval

in the worst of the case (if you dont know how to alter the interval) recreate it

[leonard905]

You cannot. You have to drop the sequence and recreate from where you would like it to start. All other parameters can be used in the alter sequence except for Start with.

[pando]

we dont change start with value, we change minvalue value

SQL> create sequence seq
  2  start with 1
  3  increment by 1
  4  nocache
  5  nocycle
  6  minvalue 1;

Sequence created.

SQL> select seq.nextval from dual;

   NEXTVAL
----------
         1

SQL> r
  1* select seq.nextval from dual

   NEXTVAL
----------
         2

SQL> r
  1* select seq.nextval from dual

   NEXTVAL
----------
         3

SQL> alter sequence seq minvalue 0 increment by -3

Sequence altered.

SQL> select seq.nextval from dual;

   NEXTVAL
----------
         0

SQL> alter sequence seq increment by 1;

Sequence altered.

SQL> select seq.nextval from dual;

   NEXTVAL
----------
         1

Back to business



SQL> select seq.nextval from dual;

   NEXTVAL
----------
         2

SQL> r
  1* select seq.nextval from dual

   NEXTVAL
----------
         3

[dba_admin]

Pando is correct and excellent!

[yls177]

Hi.

I am on 9.2.0.5. I have the problem as belows

SQL> select seq.nextval from dual
2 ;
select seq.nextval from dual
*
ERROR at line 1:
ORA-02289: sequence does not exist

Please advise.

[kris123]

your sequence named "seq" is not there....try
sql> create sequence seq;

Ms Congeniality III

[reydp]

Originally posted by yls177
Hi.

I am on 9.2.0.5. I have the problem as belows

SQL> select seq.nextval from dual
2 ;
select seq.nextval from dual
*
ERROR at line 1:
ORA-02289: sequence does not exist

Please advise.

check who's the owner of the seq.
you might need to create synonym.