Home » SQL & PL/SQL » SQL & PL/SQL » Oracle 11g Sequence(merged) (11g)
Oracle 11g Sequence(merged) [message #679391] Thu, 27 February 2020 06:04 Go to next message
moyida
Messages: 4
Registered: May 2019
Junior Member
Can you please advise, in which column oracle Sequence current value stores.
Oracle 11g Sequence value to reset to a different value [message #679392 is a reply to message #679391] Thu, 27 February 2020 06:07 Go to previous messageGo to next message
moyida
Messages: 4
Registered: May 2019
Junior Member
Please advise how to reset oracle sequence value to a different value without using .nextval.
Re: Oracle 11g Sequence stores current value [message #679394 is a reply to message #679391] Thu, 27 February 2020 06:40 Go to previous messageGo to next message
EdStevens
Messages: 1207
Registered: September 2013
Senior Member
moyida wrote on Thu, 27 February 2020 06:04
Can you please advise, in which column oracle Sequence current value stores.
It's not stored in a "column" (or a table) at all. It's stored in a memory cache. Based on your other post it appears that you want to change the current value, and this question suggests you think you can do it with a simple SQL UPDATE. It doesn't work that way. Assuming there is a good reason for changing the 'current' value, you would use ALTER SEQUENCE.

But I'd be remiss if I did not ask why you want to change it? What is the business problem? As a matter of getting help from others, it is far, far better to create one single post and state the business problem instead of multiple little posts asking questions specific to your pre-conceived solution to that problem.
Re: Oracle 11g Sequence value to reset to a different value [message #679395 is a reply to message #679392] Thu, 27 February 2020 06:41 Go to previous messageGo to next message
EdStevens
Messages: 1207
Registered: September 2013
Senior Member
moyida wrote on Thu, 27 February 2020 06:07
Please advise how to reset oracle sequence value to a different value without using .nextval.
See my reply to your other post.
Re: Oracle 11g Sequence value to reset to a different value [message #679396 is a reply to message #679395] Thu, 27 February 2020 07:51 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
As far as I'm aware you can't use ALTER SEQUENCE by itself to change the current sequence value.
You can use it conjunction with nextval by temporarily changing the increment by.

Alternatively you can drop and recreate the sequence and specify a new start with value.
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 Go to previous messageGo to next message
Bill B
Messages: 1955
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 #679404 is a reply to message #679403] Thu, 27 February 2020 12:24 Go to previous messageGo to next message
Michel Cadot
Messages: 67154
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
and you would have to recompile all the invalids.
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.

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 Go to previous message
Bill B
Messages: 1955
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
Previous Topic: How to Fetch merged rows back !
Next Topic: Time Between Consecutive Locations
Goto Forum:
  


Current Time: Thu Jun 04 15:49:19 CDT 2020