5

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.

Community
  • 1
  • 1
Brijesh
  • 191
  • 1
  • 5
  • 11
  • 1
    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: https://community.oracle.com/thread/2512696 For decreasing you can drop and recreate your sequence, it that is an option for you. – Manuel Apr 05 '17 at 11:35
  • The correct syntax is: ALTER SEQUENCE MSG_MESSAGE_SEQUENCE RESTART **START** WITH 97; – Alessio Frabotta Oct 25 '21 at 14:03

1 Answers1

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';
Community
  • 1
  • 1
CristiC
  • 22,068
  • 12
  • 57
  • 89