Hi ,
Can someone suggest the best way to rename a sequence keeping the currval, the same.
-Sravan
Printable View
Hi ,
Can someone suggest the best way to rename a sequence keeping the currval, the same.
-Sravan
drop it, then create a new one, start with desired value.
Thanks for the response.
I have lots of sequences and need to script them. Is there any quick way.
well, you can do something ilke :
select 'create sequence ' || sequence_name || ' start with ' || sequence_name.nextval || ';'
from user_sequences;
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.
Make sure that you modify your old sequences to set nocache on. Otherwise your new renamed sequence will have the cache sequences incremented.
SQL> select junkseq.nextval from dual;
NEXTVAL
----------
100
SQL> rename junkseq to newseq;
Table renamed.
SQL> select junkseq.nextval from dual;
select junkseq.nextval from dual
*
ERROR at line 1:
ORA-02289: sequence does not exist
SQL> select newseq.nextval from dual;
NEXTVAL
----------
101
[Edited by kris109 on 06-28-2001 at 12:11 PM]
Gee, this is a nice trick! So simple, yet so elegant.Quote:
Originally posted by kris109
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.
And as I can see it now from the documentation it is even documented! So besides tables and views (for which most of us was aware of, I gues) you can use RENAME also for sequences and for private synonyms.
Sweet. A compliment from jmodic. Now I can die peacefully.
LOL!!! :D