|
|
|
|
|
Re: Oracle 11g Sequence value to reset to a different value [message #679403 is a reply to message #679396] |
Thu, 27 February 2020 12:12 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
You can change a sequences nextval by using the following techniques. For this example I will be changing a sequence from 123456 to 123;
SELECT my_seq.NEXTVAL
FROM dual;
NEXTVAL
-------
123456
(123 - 123456) + 1 = -123332
ALTER SEQUENCE my_seq INCREMENT BY -123332;
SELECT my_seq.NEXTVAL
FROM dual;
NEXTVAL
-------
123
ALTER SEQUENCE my_seq INCREMENT BY 1;
You can also drop and recreate the sequence but this will invalidate all code that uses the sequence and you would have to recompile all the invalids.
[Updated on: Thu, 27 February 2020 12:18] Report message to a moderator
|
|
|
|
Re: Oracle 11g Sequence value to reset to a different value [message #679405 is a reply to message #679404] |
Thu, 27 February 2020 12:27 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
Michel Cadot wrote on Thu, 27 February 2020 13:24
Well, Oracle will do it automatically and transparently.
The main problem would be with the current code in SGA or PGA like package variables which may lead to application errors.
true, but I always recompile so I don't get support calls on failing applications
|
|
|