Re: Question about Sequences

Ok, below the script I developed for this situation for an interactive session.
The sequence name and new next value are prompted in this script but I assume you can figure out how to get the sequence name the max value +1 from the the table column used for the sequence into SQL*Plus variables and pass them to this script. Remove the accept that enables an escape too.

How does it work
It calculates the difference between the current next value of the sequence and the wanted new next value, alters the sequence so this difference is the new increment, select the nextvalue once, and alters the sequence back to original interval. You can both increase or lower the new wanted next value, does not matter.
To be able to alter the sequence you must be the owner of it.

The disadvantage of dropping and recreating is that grants and synonyms are lost.

• begin sql script --- set ver off set feedback off

prompt
accept P_SEQNAME prompt "Enter name of sequence to alter: " accept P_NEXTVAL number prompt "Enter next_value to set to: "

col curval new_value p_curval head "Next value before"
col curinc new_value p_curinc head "Current incr."
col newinc new_value p_newinc head "New incr."

prompt
prompt Altering next value of sequence &&p_seqname

prompt =========================================================

SELECT &&p_seqname..nextval curval
FROM dual
;

SELECT increment_by curinc
, (&&p_nextval - &&p_curval - 2) newinc
FROM user_sequences
WHERE sequence_name = UPPER('&&p_seqname') ;

prompt Enter to continue altering the sequence &&p_seqname, press Ctrl-C to abort
accept P_YN prompt "After abort"

prompt
prompt Alter the sequence increment
ALTER SEQUENCE &&p_seqname
INCREMENT BY &&p_newinc
;

SELECT &&p_seqname..nextval "Next value with new incr." FROM dual
;

prompt
prompt Alter the sequence increment to the original value ALTER SEQUENCE &&p_seqname
INCREMENT BY &&p_curinc
;

SELECT &&p_seqname..nextval "Next value after" FROM dual
;

SELECT *
FROM user_sequences
WHERE sequence_name = UPPER('&&p_seqname') ;

spool off

undef p_seqname
undef p_curinc
undef p_newinc
undef p_nextval
undef p_curval
undef p_yn

• end sql script ---

Ryan Gaffuri <rgaffuri_at_cox.net> schreef in berichtnieuws 1efdad5b.0206050710.4af26dd4_at_posting.google.com...
| Someone on my project recently truncated all the tables in one of our
| databases and imported new data. The problem is that the our sequence
| generators did not reset to match the highest sequence we have in our
| primary keys, so we keep hitting a unique constraint.
|
| Is there a command to reset sequences to the highest value in the
| table?
| Id prefer to run this dynamically than have to find the highest value
| for each sequence add set it to n + 1. This would be time consuming.
|
| Thanks.
Received on Wed Jun 05 2002 - 17:12:45 CDT