create sequence s start with 1 increment by 1 Ĭreate or replace procedure reset_sequence ( i_buffer in pls_integer default 0) The value for maxvalue has to be greater than the current nextval, so the procedure below includes an optional parameter allowing a buffer in case the sequence is accessed again between selecting the nextval in the procedure and setting the cycle property. The advantage of this method compared to setting a negative increment by is the sequence can continue to be used while the reset process runs, reducing the chance you need to take some form of outage to do the reset. When the nextval of the sequence hits the maxvalue, if the cycle property is set then it will begin again from the minvalue of the sequence. There is another way to reset a sequence in Oracle: set the maxvalue and cycle properties. In my actual database there are around one hundred other sequences being reset through this mechanism, so there are 97 more calls to Reset_Sequence_to_Data in that procedure above. Reset_Sequence_to_Data( 'BATCH', 'BATCHID' ) Reset_Sequence_to_Data( 'JOBSTATE', 'JOBID' ) Reset_Sequence_to_Data( 'ACTIVITYLOG', 'LOGID' ) The final piece in this puzzle and the icing on the cake comes next. The procedure above will check for a field's max value in a table, builds a sequence name from the table/field pair and invokes "Reset_Sequence" with that sensed max value. Reset_Sequence( p_TableName || '_' || p_Fieldname || '_SEQ', l_MaxUsed ) 'select coalesce(max(' || p_FieldName || '),0) from '|| p_TableName into l_MaxUsed That procedure is useful all by itself, but now let's add another one which calls it and specifies everything programmatically with a sequence naming convention and looking for the maximum value used in an existing table/field. 'alter sequence ' || p_seq_name || ' increment by 1 minvalue ' || l_minvalue 'select ' || p_seq_name || '.nextval from dual' INTO l_difference 'alter sequence ' || p_seq_name || ' increment by ' || l_difference || 'select ' || p_seq_name || '.nextval from dual' INTO l_current L_minvalue user_sequences.min_value%type := 0 Procedure Reset_Sequence( p_seq_name in varchar2, p_val in number default 0) It may in fact push the next value ever higher if the desired p_val or existing minvalue are higher than the current or calculated next value.īest of all, it can be called to reset to a specified value, and just wait until you see the wrapper "fix all my sequences" procedure at the end. I end up calling this proc from another script which executes multiple calls for a whole bunch of sequences, resetting nextval back down to some level which is high enough to not cause primary key violations where I'm using the sequence's value for a unique identifier. Why? I like to call the thing resetting the sequence back to the max ID used in some table. My approach is a teensy extension to Dougman's example.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |