Tip of the Week
Tip for Week of January 4, 2004

Fast Reset Sequences (Alternative)

This tip comes from Ilya Petrenko, Senior DBA at ICT Group, Inc. USA, in Newtown, PA.

set ver off
def SeqOwner=&Owner
def SeqName=&SequenceName

-- Save Current Settings
col Cur_Increment_By    new_value Seq_Increment_By
col Cur_Min_Value       new_value Seq_MinValue
col Cur_Cache_Size      new_value Seq_Cache
select
        Increment_By    Cur_Increment_By,
        Min_Value       Cur_Min_Value,
        DECODE(Cache_Size,0,'NoCache','Cache '||Cache_Size)
Cur_Cache_Size
from    all_sequences
where
    sequence_owner=UPPER('&SeqOWNER')
and sequence_name=UPPER('&SeqName')
and rownum<2;


-- Calculate Increment_By and MinValue for "BACK" Operation
col Value1 new_value Seq_Increment_By_Back
col Value2 new_value Seq_MinValue_Back
select
        -1*(&SeqOwner..&SeqName..NextVal -&Seq_MinValue) Value1,
        -1*(&Seq_Increment_By - &Seq_MinValue)           Value2
from dual;


-- Modify for Reset
alter sequence &SeqOwner..&SeqName increment by &Seq_Increment_By_Back
MinValue &Seq_MinValue_Back;


-- Reset even Deeper (Increment_By Low than Original MinValue) select
&SeqOwner..&SeqName..NextVal from dual;


-- Return Back Increment_By , and Reset Current Cache Value alter
sequence &SeqOwner..&SeqName increment by &Seq_Increment_By NOCACHE;


-- Return Old(Current) Values Back such as Min_Value anc Cache Size (if
exists) alter sequence &SeqOwner..&SeqName increment by
&Seq_Increment_By MinValue &Seq_MinValue &Seq_Cache;

set ver off

DEFINE SeqOwner=SBC
DEFINE SeqName=Seq1

select &SeqOwner..&SeqName..NextVal from dual;


   NEXTVAL
----------
        55

SEQUENCE_NAME         MIN_VALUE  MAX_VALUE INCREMENT_BY CACHE_SIZE LAST_NUMBER
-------------------- ---------- ---------- ------------ ---------- -----------
SEQ1                        -19 1.0000E+27            5         30 160

CUR_INCREMENT_BY CUR_MIN_VALUE CUR_CACHE_SIZE
---------------- ------------- ----------------------------------------------
               5           -19 Cache 30 

    VALUE1     VALUE2
---------- ----------
       -79        -24

SQL> -- Reset even Deeper (Increment_By Low than MinValue)

   NEXTVAL
----------
       -19

SEQUENCE_NAME        CURRENT_MIN CURRENT_MAX CURRENT_STEP CACHE_SIZE LAST_NUMBER
-------------------- ----------- ----------- ------------ ---------- -----------
SEQ1                         -24  1.0000E+27          -79         30 -98


SQL> -- Return Back Increment_By , and Reset Current Cache Value

SEQUENCE_NAME        CURRENT_MIN CURRENT_MAX CURRENT_STEP CACHE_SIZE LAST_NUMBER
-------------------- ----------- ----------- ------------ ---------- -----------
SEQ1                         -24  1.0000E+27            5          0 -14

SQL> -- Return Old(Current) Values Back such as Min_Value anc Cache Size

SQL> (if exists)

SEQUENCE_NAME        CURRENT_MIN CURRENT_MAX CURRENT_STEP CACHE_SIZE LAST_NUMBER
-------------------- ----------- ----------- ------------ ---------- -----------
SEQ1                         -19  1.0000E+27            5         30 -14