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.
| >
|
|
Received on Thu Jun 06 2002 - 02:50:41 CDT
