How do you refresh (reset) a sequence so that it start from the beginning?
Printable View
How do you refresh (reset) a sequence so that it start from the beginning?
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
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.
we dont change start with value, we change minvalue value
Code:
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
Pando is correct and excellent!
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.
:D your sequence named "seq" is not there....try
sql> create sequence seq;
Ms Congeniality III:p
check who's the owner of the seq.Quote:
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.
you might need to create synonym.