rename a sequence
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: rename a sequence

  1. #1
    Join Date
    Apr 2001
    Posts
    18
    Hi ,
    Can someone suggest the best way to rename a sequence keeping the currval, the same.

    -Sravan

  2. #2
    Join Date
    Jun 2001
    Posts
    132
    drop it, then create a new one, start with desired value.
    You think I'm going to have an affair with you? --Stanley Kowalski

  3. #3
    Join Date
    Apr 2001
    Posts
    18
    Thanks for the response.
    I have lots of sequences and need to script them. Is there any quick way.

  4. #4
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    well, you can do something ilke :

    select 'create sequence ' || sequence_name || ' start with ' || sequence_name.nextval || ';'
    from user_sequences;

  5. #5
    Join Date
    May 2001
    Location
    San Francisco, California
    Posts
    510
    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]

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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.
    Gee, this is a nice trick! So simple, yet so elegant.

    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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  7. #7
    Join Date
    May 2001
    Location
    San Francisco, California
    Posts
    510
    Sweet. A compliment from jmodic. Now I can die peacefully.

  8. #8
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    LOL!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width