rename sequence in other schema with same currval

sql sequence rename alter nextval
Anu picture Anu · Aug 19, 2015 · Viewed 10.9k times · Source

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

Ankit Bajpai picture Ankit Bajpai · Aug 19, 2015

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>;

Related questions

How do I reset a sequence in Oracle?

In PostgreSQL, I can do something like this: ALTER SEQUENCE serial RESTART WITH 0; Is there an Oracle equivalent?

Read More
How to retrieve the current value of an oracle sequence without increment it?

Is there an SQL instruction to retrieve the value of a sequence that does not increment it. Thanks. EDIT AND CONCLUSION As stated by Justin Cave It's not useful to try to "save" sequence number so select a_seq.nextval …

Read More
How to create an Oracle sequence starting with max value from a table?

Trying to create a sequence in Oracle that starts with the max value from a specific table. Why does this not work? CREATE SEQUENCE transaction_sequence MINVALUE 0 START WITH (SELECT MAX(trans_seq_no) FROM TRANSACTION_LOG) INCREMENT BY 1 CACHE 20;

Read More