Very useful Tom. I like the way you are thinking and solving problems.

Hi,

A really nifty and innovative work to reset sequences. But then wouldn't it be more efficient to drop and recreate the sequence?

Just wondering...

It is better to reset then drop/create.

The major #1 reason is that dependent objects will NOT go invalid during a reset, they will during a drop/create cycle.  If you have stored procedures/triggers dependent on this sequence -- dropping would be bad.  Consider:

ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace procedure p
  2  as
  3          l_number number;
  4  begin
  5          select my_seq.nextval into l_number from dual;
  6  end;
  7  /
Procedure created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select object_type, object_name, status
  2  from user_objects
  3  where status = 'INVALID'
  4  order by object_type, object_name
  5  /
no rows selected

ops$tkyte@ORA817DEV.US.ORACLE.COM> drop sequence my_seq;
Sequence dropped.

ops$tkyte@ORA817DEV.US.ORACLE.COM> create sequence my_seq;
Sequence created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select object_type, object_name, status
  2  from user_objects
  3  where status = 'INVALID'
  4  order by object_type, object_name
  5  /

OBJECT_TYPE        OBJECT_NAME                    STATUS
------------------ ------------------------------ ----------
PROCEDURE          P                              INVALID

<b>the very next time the procedure is run, it'll be implicitly compiled.  This adds overhead -- especially if you have LOTS of dependent objects</b>

ops$tkyte@ORA817DEV.US.ORACLE.COM> begin
  2          for i in 1 .. 100
  3          loop
  4                  p;
  5          end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

<b>so the procedure is valid again -- now, we'll "reset it"</b>

ops$tkyte@ORA817DEV.US.ORACLE.COM> select my_seq.nextval from dual;

   NEXTVAL
----------
       101

ops$tkyte@ORA817DEV.US.ORACLE.COM> exec reset_seq( 'my_seq' )
PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select my_seq.nextval from dual;

   NEXTVAL
----------
         1

ops$tkyte@ORA817DEV.US.ORACLE.COM> select object_type, object_name, status
  2  from user_objects
  3  where status = 'INVALID'
  4  order by object_type, object_name
  5  /

no rows selected

<b>and this time the procedure is NOT invalid -- thats better. </b> 

This is cool and informative.

We are using ORACLE Sequences for our PK. They are generated by a JAVA app call "select hs_seq.nextval from dual"
The problem is, we call this many, many times & notice we are taking a hit waiting for SQL*NET messaging.
We would like to have the app call a function/proc to get the next seq number & increment it by 5000 & let the app handle the PK's. My question is, what would the best way to accomplish this, keeping in mind, there may be more than one session calling this?

I only wish it was that easy!

The application has a convaluted way of using these unique keys generated by a sequence. They build up all the inserts required & send them all together in 1 big batch. They require the sequence numbers when they are building the statements.
Don't ask why! This is why I would like to pull in perhaps 5k at a time. Perhaps using:
Getting the nextval
then incrementing the SEQ by 5000.
What do you think?

Hi,

firstly thanks for such an informative discussion!

I have a question about the suggestion to use "..returning into ?" clause to retrieve the sequence value just used in an insert statement. I know that you can set values in a java PreparedStatement by using setInt(..) etc. But how do we retrieve an int value from a PreparedStatement execution? (the returned value being the parameter to the "..returning into" clause).

Thanks and Regards,
~ amol

Hi Tom,
I have a sequence like this:

create sequence s
 minvalue 2007000000000 maxvalue 2007999999999
 increment by 1 start with 2007000000000
 cache 10 order nocycle;
 
Sequence created
select to_char(s.nextval) from dual;
 
TO_CHAR(S.NEXTVAL)
----------------------------------------
2007000000000

but after execution of your reset_seq procedure I have nextval=1

exec reset_seq('S');
 
select to_char(s.nextval) from dual;

TO_CHAR(S.NEXTVAL)
----------------------------------------
1

How you can see, nextval after reset_seq, differ from nextval after sequence creation.

This is my workaround

declare
  l_seqname user_sequences.sequence_name%type := 'MIRDCONT_SEQ' ;
  l_val number;
  l_minvalue user_sequences.min_value%type;
  l_maxvalue user_sequences.max_value%type;
  l_exec varchar2(1000);
begin

  select t.min_value
        ,t.max_value
    into l_minvalue
        ,l_maxvalue
    from user_sequences t
   where sequence_name = l_seqname;
  dbms_output.put_line('l_minvalue = '||l_minvalue);
  dbms_output.put_line('l_maxvalue = '||l_maxvalue);

  l_exec := 'select ' || l_seqname || '.nextval from dual';
  execute immediate l_exec into l_val;
  dbms_output.put_line(l_exec||'='||l_val);

  l_exec := 'alter sequence ' || l_seqname || ' increment by -' || l_val || ' minvalue -99999999999999999999 '||' maxvalue 99999999999999999999';
  dbms_output.put_line('EXEC: '||l_exec);
  execute immediate l_exec;

  l_exec := 'select ' || l_seqname || '.nextval from dual';
  execute immediate l_exec into l_val;
  dbms_output.put_line('ESEGUITO: '||l_exec||'='||l_val);

  l_exec := 'alter sequence ' || l_seqname || ' increment by 1';
  execute immediate l_exec;
  dbms_output.put_line('ESEGUITO: '||l_exec);

  l_exec := 'alter sequence ' || l_seqname || ' increment by '||l_minvalue;
  execute immediate l_exec;
  dbms_output.put_line('EXECUTED: '||l_exec);
  l_exec := 'select ' || l_seqname || '.nextval from dual';
  execute immediate l_exec into l_val;
  dbms_output.put_line('EXECUTED: '||l_exec||'='||l_val);

  l_exec := 'alter sequence ' || l_seqname || ' increment by 1';
  execute immediate l_exec;
  dbms_output.put_line('EXECUTED: '||l_exec);

  l_exec := 'select ' || l_seqname || '.nextval from dual';
  execute immediate l_exec into l_val;
  dbms_output.put_line('EXECUTED: '||l_exec||'='||l_val);


  l_exec := 'alter sequence ' || l_seqname || ' minvalue '||l_minvalue;
  dbms_output.put_line('EXEC: '||l_exec);
  execute immediate l_exec;

  l_exec := 'alter sequence ' || l_seqname || ' maxvalue '||l_maxvalue;
  dbms_output.put_line('EXEC: '||l_exec);
  execute immediate l_exec;
end;
/

Con you "optimize" my implementation?

Thanks in advance.

ok Tom,
but I prefer don't drop and recreate because it invalidate my objects

I've modified Tom's procedure....

create or replace procedure reset_seq( p_seq_name in varchar2 ) is
  l_val      number;
  l_minvalue user_sequences.min_value%type;

begin

  select t.min_value
  into   l_minvalue
  from   user_sequences t
  where  sequence_name = p_seq_name;
   
  execute immediate
     'select ' || p_seq_name || '.nextval from dual' INTO l_val;
    
  l_val := l_val - l_minvalue;
 
  execute immediate
     'alter sequence ' || p_seq_name || ' increment by -' || l_val || 

  execute immediate
      'select ' || p_seq_name || '.nextval from dual' INTO l_val;

  execute immediate
     'alter sequence ' || p_seq_name || ' increment by 1 minvalue '||l_minvalue;
end;
/

Sorry, please ignore previous code as there are compilation errors...I hope this is complete script.

create or replace procedure reset_seq( p_seq_name in varchar2) is
  l_val      number;
  l_minvalue user_sequences.min_value%type;

begin

  select min_value
  into   l_minvalue
  from   user_sequences
  where  sequence_name = p_seq_name;

  execute immediate
       'select ' || p_seq_name || '.nextval from dual' INTO l_val;

 l_val := l_val - l_minvalue;

  execute immediate
       'alter sequence ' || p_seq_name || ' increment by -' || l_val ||
                                                             ' minvalue '||l_minvalue;

  execute immediate
      'select ' || p_seq_name || '.nextval from dual' INTO l_val;

  execute immediate
     'alter sequence ' || p_seq_name || ' increment by 1 minvalue '||l_minvalue;
end;
/

Thank you so much for this help..

I need one more help.

When i am executing this SP as a command , its working fine.
Command is : exec SP_reset_sequence('SOL_SRNO');
But if i am going to call it from another SP then it gives an Error

The SP is:
CREATE OR REPLACE PROCEDURE SP_UPDATE_FIN_SOLMASTER
AS
BEGIN

execute immediate 'exec SP_reset_sequence(''SOL_SRNO'')';
-- Some other text
END

If i comment the execute immidiate syntax then this sp works fine. but as i uncomment it, it complied properly but on execution time it gives following error

ORA-20001: An error was encountered - -900 -ERROR- ORA-00900: invalid SQL statement
ORA-06512: at "CDPTEST.SP_UPDATE_FIN_SOLMASTER", line 64
ORA-06512: at line 2


can you please help me for the same.

Thank you

CREATE OR REPLACE PROCEDURE SP_UPDATE_FIN_SOLMASTER
AS
BEGIN

   SP_reset_sequence('SOL_SRNO');
-- Some other text
END

SQL> exec p('inputs')

SQL> begin
  2     p;
  3  end;
  4  /

Pass in the seed value as a parameter. Why? I like to call the thing resetting the sequence back to the max ID used in some table. 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.

It also honors the previous minvalue. 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.

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

create or replace
procedure Reset_Sequence( p_seq_name in varchar2, p_val in number default 0)
is
l_current number := 0;
l_difference number := 0;
l_minvalue user_sequences.min_value%type := 0;

begin

select min_value
into l_minvalue
from user_sequences
where sequence_name = p_seq_name;

execute immediate
'select ' || p_seq_name || '.nextval from dual' INTO l_current;

if p_Val < l_minvalue then
l_difference := l_minvalue - l_current;
else
l_difference := p_Val - l_current;
end if;

if l_difference = 0 then
return;
end if;

execute immediate
'alter sequence ' || p_seq_name || ' increment by ' || l_difference ||
' minvalue ' || l_minvalue;

execute immediate
'select ' || p_seq_name || '.nextval from dual' INTO l_difference;

execute immediate
'alter sequence ' || p_seq_name || ' increment by 1 minvalue ' || l_minvalue;
end Reset_Sequence;

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

create or replace
procedure Reset_Sequence_to_Data(
p_TableName varchar2,
p_FieldName varchar2
)
is
l_MaxUsed NUMBER;
BEGIN

execute immediate
'select coalesce(max(' || p_FieldName || '),0) from '|| p_TableName into l_MaxUsed;

Reset_Sequence( p_TableName || '_' || p_Fieldname || '_SEQ', l_MaxUsed );

END Reset_Sequence_to_Data;

Now we're cooking with gas!

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.

The final piece in this puzzle and the icing on the cake comes next...

create or replace
procedure Reset_All_Sequences
is
BEGIN

Reset_Sequence_to_Data( 'ACTIVITYLOG', 'LOGID' );
Reset_Sequence_to_Data( 'JOBSTATE', 'JOBID' );
Reset_Sequence_to_Data( 'BATCH', 'BATCHID' );

END Reset_All_Sequences;

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.

#!/usr/bin/ksh
# -----------------------------------------------------------------
# Reset_Sequence.dbo
# -----------------------------------------------------------------
#

if [ $# -lt 1 ]
then
echo "Usage: "`basename $0`" <Schema>.<Sequence> <Valeur>|\"Query\" "
echo "Usage: "`basename $0`" DBAPROD.CDE_SEQ 9999 "
echo "Usage: "`basename $0`" DBAPROD.CDE_SEQ \"select NVL(max(NU_CDE),0) + 1 from DBAPROD.COMMANDE where status = 0 \" "

exit 1
fi

echo "Reset de la Sequence $1 a la valeur : $2 "
sqlplus -s / <<EOF
whenever sqlerror exit failure;
whenever oserror exit failure;
set serveroutput on
set feed off
declare
valeur number;
cible number;
min_original number;
incr_original number;
begin
if length('$2') > 10 then
execute immediate '$2' into cible;
else
cible := '$2';
end if;
select min_value,increment_by
into min_original,incr_original
from dba_sequences
where sequence_owner || '.' || sequence_name = upper('$1');
select $1.nextval into valeur from dual;
dbms_output.put_line('$1 : Valeur 1 : ' || to_char(valeur) );
valeur := ( valeur ) * (-1);
execute immediate 'Alter sequence $1 minvalue 0 increment by ' || valeur;
select $1.nextval into valeur from dual;
dbms_output.put_line('$1 : Valeur 2 : ' || to_char(valeur) );
execute immediate 'Alter sequence $1 minvalue 0 increment by ' || ( cible - incr_original);
select $1.nextval into valeur from dual;
dbms_output.put_line('$1 : Valeur 3 : ' || to_char(valeur) );
execute immediate 'Alter sequence $1 minvalue ' || min_original || ' increment by ' || incr_original;
dbms_output.put_line('$1 : Prochaine valeur : ' || (valeur + incr_original) );
end;
/
exit $?

Hi Tom,
I am running similar logic as you in a unnamed PL/SQL block, but somehow the 2nd Alter statement (line 15,16) in your code reset the sequence back to original increment value. If i just do First Alter and Next Val then it works fine but then my increment value is not reset.
Do you know if there is any limitation in running 2 Alter statements in one PL/SQL block

alter sequence s restart start with :val