reset the sequence

[kavithared]

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

[marist89]

http://otn.oracle.com/docs/products/.../views.htm#581

[kavithared]

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

[amar]

Kavitha,

The following thread might be of interest to you:

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

-amar

[stecal]

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?

[amar]

Hi stecal,

Have you tried Pando's method?

-amar

[stecal]

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;

[kavithared]

hii any how many thanks