4

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.

Anu
  • 176
  • 1
  • 3
  • 14

3 Answers3

3

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>;
Ankit Bajpai
  • 13,128
  • 4
  • 25
  • 40
3

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.

Calapacho
  • 63
  • 3
  • 1
    Unfortunately 'START WITH' takes a constant, so your solution can only be done manually, not via script. – dokaspar Apr 12 '18 at 08:01
1

You can rename it straight out:

rename old_sequence to new_sequence;

It would say table renamed but you can ignore it. Your sequence will be renamed.

Try it.

Abhay Chauhan
  • 404
  • 3
  • 11
  • no. As mentioned, the owner of the sequence is difference. It doesnt allow direct renaming of the sequence. – Anu Aug 19 '15 at 10:01