How to change Sequence LAST_NUMBER in Oracle [duplicate]

Question

Possible Duplicate:
How do I reset a sequence in Oracle?

I would like to change one parameter of sequence LAST_NUMBER to some number, how can i do it through SQL query.

ALTER SEQUENCE MSG_MESSAGE_SEQUENCE RESTART WITH 97

And it is not working.

If you want to increase the value, use a big increment (diff between desired and actual) for one nextval-call and then reset increment to real value: ALTER SEQUENCE mySeq INCREMENT BY 1000004; SELECT mySeq.nextval from dual; ALTER SEQUENCE mySeq INCREMENT BY 1; source: community.oracle.com/thread/2512696 For decreasing you can drop and recreate your sequence, it that is an option for you. — Manuel
– Manuel, Commented Apr 5, 2017 at 11:35
The correct syntax is: ALTER SEQUENCE MSG_MESSAGE_SEQUENCE RESTART START WITH 97; — Alessio Frabotta
– Alessio Frabotta, Commented Oct 25, 2021 at 14:03

Community · Accepted Answer · 2017-05-23 12:17:32Z

1

From here:

'select MSG_MESSAGE_SEQUENCE.nextval from dual' INTO l_val;

execute immediate
'alter sequence ' || p_seq_name || ' increment by 97 - ' || l_val || ' minvalue 97';

edited May 23, 2017 at 12:17

CommunityBot

11 silver badge

answered Jul 1, 2011 at 13:30

CristiC

22.7k13 gold badges62 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to change Sequence LAST_NUMBER in Oracle [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related