How can I reset all sequences in my Oracle DB to value 0?

Question

I want to reset all sequences to 0. This somehow does not work. Can you say why?

create or replace
procedure reset_seq( p_seq_name in varchar2 )
is
    l_val number;
begin
    execute immediate
    'select ' || p_seq_name || '.nextval from dual' INTO l_val;

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

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

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

This script was from stack overflow and it works for single sequences.

begin
for i in (select sequence_name from dba_sequences where sequence_name like '%SEQ_PR%') LOOP
execute immediate 'reset_seq('||i.sequence_name||')';
end loop;
end;
/

This one was written by myself :)

Error report - ORA-00900: invalid SQL statement ORA-06512: at line 3 00900. 00000 - "invalid SQL statement"

Littlefoot Littlefoot · Accepted Answer · 2019-02-01T10:48:34

You don't need dynamic SQL.

begin
for i in (select sequence_name from dba_sequences where sequence_name like '%SEQ_PR%') LOOP
  reset_seq(i.sequence_name);           --> this is just fine
end loop;
end;
/

Example (I'm using USER_SEQUENCES instead):

SQL> select seq_pr1.nextval from dual;

   NEXTVAL
----------
         9

SQL> select seq_pr2.nextval from dual;

   NEXTVAL
----------
         8

SQL> begin
  2  for i in (select sequence_name from user_sequences where sequence_name like '%SEQ_PR%') LOOP
  3    reset_seq(i.sequence_name);
  4  end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> select seq_pr1.nextval from dual;

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

SQL> select seq_pr2.nextval from dual;

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

SQL>

How can I reset all sequences in my Oracle DB to value 0?

1 Answers