Re: Question about Sequences

WAIT a minute!

Let's be clear, very clear, on this!

You are getting the root value to do your sequence number stuff out of tables and not out of sequences, correct?! I think that's what you said, but I want to be sure of that. And yes I read the script.

In plainer English, if your strategy is to identify the highest currently used sequence number value as stored in a table column, and reset the corresponding sequence to a value above that (please use 100, not 1 above the last used value, at least; trust me, it makes backtracking easier if you have a glaring sequence gap)., that sounds okay... if you're using sequences to do things like I've sometimes done, say to decompose enormous 20 column keys into a sequence, and everything is tied together in that way, I'd be ten times more careful to be absolutely, positively sure.

As far as the new value, depending on your CACHE settings, make the new initial values outrageously larger, so it is easier to figure out things later on. Remember, if, say , a sequence is defined with a CACHE of 10, but only two sequence requests are made and used, the remaining 8 can be lost under certain database crash/restart/shutdown situations, and when Oracle comes up, the next available bucket will be the next batch of ten; the prior unused 8 slots are permanently MIA.

Oh, please do really good backups!!

RSH. "Anton Buijs" <aammbuijs_at_xs4all.nl> wrote in message news:adm2b4$4mu$1_at_news1.xs4all.nl...
> 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:49:31 CDT