title: How can I reset all sequences in my Oracle DB to value 0?
body: <p>I want to reset all sequences to 0. This somehow does not work. Can you say why?</p>
  
  <pre><code>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;
  /
  </code></pre>
  
  <p>This script was from stack overflow and it works for single sequences.</p>
  
  <pre><code>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;
  /
  </code></pre>
  
  <p>This one was written by myself :)</p>
  
  <p>Error report -
  ORA-00900: invalid SQL statement
  ORA-06512: at line 3
  00900. 00000 -  "invalid SQL statement"</p>