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

Thread: reset the sequence

  1. #1
    Join Date
    May 2002
    Posts
    232
    Hiiii,we are migrating database from 8i to 9i,but i want some help regarding sequences,how to reset the sequence value in new database.
    If any one know can send me script.
    thanks
    kavitha
    kavitha

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092

  3. #3
    Join Date
    May 2002
    Posts
    232
    Hiii,I know that oracle doc,but i want how to reset the sequences,
    I.e we have 50 sequences in old database,their values are now differentnow i like to create all 50 sequences by defalt values,mean while if i will execuite a sql script,then all sequences must be set to last value.
    thanks
    kavitha
    kavitha

  4. #4
    Join Date
    Mar 2001
    Posts
    314
    Kavitha,

    The following thread might be of interest to you:

    http://www.dbasupport.com/forums/sho...threadid=16708

    -amar

  5. #5
    Join Date
    May 2002
    Posts
    2,645
    You cannot alter a current value of a sequence. You can increase the next value via

    alter sequence seq_name increment by number;

    To change value (like, start over), drop and re-create the sequence. You'll have to do all the grants associated with the sequences, and recompile any triggers that depend on these sequences. Also, what were the sequences used for? Were they used to create a primary key value?

  6. #6
    Join Date
    Mar 2001
    Posts
    314
    Hi stecal,

    Have you tried Pando's method?

    -amar

  7. #7
    Join Date
    May 2002
    Posts
    2,645
    Capture the following in a spool file, clean it up, save it as a sql file, and run it.

    SQL> select 'alter sequence '||sequence_name||' increment by -'||last_number||';'
    2 from user_sequences;

    'ALTERSEQUENCE'||SEQUENCE_NAME||'INCREMENTBY-'||LAST_NUMBER||';'
    ----------------------------------------------------------------------------------
    alter sequence NEXT_ROW increment by -21;
    alter sequence SEQ_ADDRESS_UUI increment by -201;
    alter sequence SEQ_CONTACT_ID increment by -104;
    alter sequence SEQ_COPYRIGHT_LINENUM increment by -81;
    alter sequence SEQ_DEV_TYPE_ID increment by -1101;
    alter sequence SEQ_LOGICAL_LINK_ID increment by -1921;
    alter sequence SEQ_OBJECT_ID increment by -863;
    alter sequence SEQ_ORG_ID increment by -269;
    alter sequence SEQ_RPT_UUI increment by -287;
    alter sequence SEQ_SR_NUM increment by -2;
    alter sequence SEQ_URL_ID increment by -321;
    alter sequence SEQ_UUI increment by -28518;
    alter sequence SEQ_WV_ID increment by -641;

  8. #8
    Join Date
    May 2002
    Posts
    232
    hii any how many thanks
    kavitha

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