rename sequence in other schema with same currval

Question

I need to rename bunch of sequences in another schema.

RENAME old_seq to new_seq doesnt work.

Tried:

ALTER SEQUENCE old_seq RENAME TO new_seq;

but it gives me error

ORA-02286: no options specified for ALTER SEQUENCE 

I do not wish to mention all the options that i had mentioned earlier while creating the sequence, since they need to be same. Only the name needs to be changed.

Answer 1

If you are not the owner of that sequence, You can use following steps:-

SELECT CURVAL FROM old_seq;

This will give you the value of Current_Sequence.

Now Drop this sequence using

DROP SEQUENCE old_seq;

And Create new Sequence with same name. Using

CREATE SEQUENCE old_seq;

And then alter that with this:-

ALTER SEQUENCE seq_example INCREMET BY <CURVAL FROM OLD VALUE>;

Answer 2

The answer from @ankit is quite in line with what's needed to fix this issue but it presents a few problems and typos which I'm fixing in this answer. I hope it'd be useful.

First, you have to select the current value for the old_seq:

SELECT old_seq.CURRVAL FROM dual;

If you get an ORA-08002 error that's because you need to ask for the NEXTVAL first in order to initialize the sequence. Just do:

SELECT old_seq.NEXTVAL FROM dual;

and then ask for CURRVAL again.

Now that you have the current value, just drop the old sequence by using

DROP SEQUENCE old_seq;

and create the new_seq with the name you want by using

CREATE SEQUENCE new_seq START WITH <CURRVAL FROM old_seq>;

If you use INCREMENT instead of START WITH, you should take into account that the increment would apply to every request for a value from the sequence. You would have to create the sequence and then reset the increment to 1. Using START WITH avoids that issue.