Re: Question about Sequences

I meant no insult by what I wrote.

If any of it was taken that way, I am deeply sorry and hope you believe me when I say, I wasn't questioning or attacking your knowlege, or your advice.

RSH.
"Anton Buijs" <aammbuijs_at_xs4all.nl> wrote in message news:adn46u$rgk$1_at_news1.xs4all.nl...
> The original question was: A sequence is used to assign a value to a PK
> column of a table. After truncate new data is imported into the tables
(like
> from a production db to a test db or so). So the next value of the
sequence
> will very likely already be used, resulting in errors. So a "resync" is
> required where the next value will be at least 1 higher than the highest
> value that exist. So I wanted to say :
> | 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 want to make a gap, adjust the script, easy. Basically I wanted to
> share the idea of changing the increment just for one select nextval to
> change it. And to prevent big accidents I added the script.
> If sequences are used otherways it might be a bad idea to use my script. I
> assume readers know what they are doing. "A fool with a tool is still a
> fool".
>
> And indead, it's a bad idea to mess around with sequence numbers in
> production databases. But that's not the intention in this case.
>
> About the CACHE I did a quick experiment with a sequence, see output
below.
> I don't think there is a problem. Loss of cached numbers is always the
case
> when the db is restarted.
>
> SQL> create sequence RSH start with 1 increment by 1 cache 20;
>
> Sequence created.
>
>
> SQL> select RSH.NEXTVAL from DUAL;
>
> NEXTVAL
> ----------
> 1
>
> SQL> /
>
> NEXTVAL
> ----------
> 2
>
> SQL> select * from user_sequences where sequence_name='RSH';
>
> SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O
> CACHE_SIZE LAST_NUMBER
> ------------------------------ ---------- ---------- ------------ - - ----

--


> ---- -----------

> RSH                                     1 1.0000E+27            1 N N

> 20          21

>

> SQL> rem Db is restarted, and sqlplus started again

>

>

> SQL> select * from user_sequences;

>

> SEQUENCE_NAME                   MIN_VALUE  MAX_VALUE INCREMENT_BY C O

> CACHE_SIZE LAST_NUMBER

> ------------------------------ ---------- ---------- ------------ - - ----

--


> ---- -----------

> RSH                                     1 1.0000E+27            1 N N

> 20          21

>

> SQL> select rsh.nextval from dual;

>

>    NEXTVAL

> ----------

>         21

>

> SQL> rem This shows cached values are lost

>

> SQL> @seqaltnextval

>

> Enter name of sequence to alter: rsh

> Enter next_value to set to:      100

>

>

> Altering next value of sequence rsh

> =========================================================

>

> Next value before

> -----------------

>                22

>

> Current incr.  New incr.

> ------------- ----------

>             1         76

> Enter to continue altering the sequence rsh, press Ctrl-C to abort

>

> Alter the sequence increment

>

> Next value with new incr.

> -------------------------

>                        98

>

> Alter the sequence increment to the original value

>

> Next value after

> ----------------

>               99

>

> SEQUENCE_NAME                   MIN_VALUE  MAX_VALUE INCREMENT_BY C O

> CACHE_SIZE LAST_NUMBER

> ------------------------------ ---------- ---------- ------------ - - ----

--


> ---- -----------

> RSH                                     1 1.0000E+27            1 N N

> 20         119

>

>

> RSH <RSH_Oracle_at_worldnet.att.net> schreef in berichtnieuws

> %zwL8.23130$LC3.1732632_at_bgtnsc04-news.ops.worldnet.att.net...

> | 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.

> | >

> |

> |

>