Oracle重置序列(不删除重建方式)

DECLARE
  CURSOR cur_sequence IS
    SELECT do.owner || '.' || do.object_name sequence_name
      FROM dba_objects do
     WHERE do.object_type = 'SEQUENCE'
       AND do.owner IN ('XX')
       AND rownum = 1;
  l_start_num NUMBER;
  l_sql       VARCHAR2(1000);
  l_count     NUMBER := 0;


BEGIN
  FOR rec_seq IN cur_sequence LOOP
    l_count := l_count + 1;
    EXECUTE IMMEDIATE 'select ' || rec_seq.sequence_name || '.nextval from dual'
      INTO l_start_num;
    l_start_num := - (l_start_num) + 10000;
    l_sql        := 'alter sequence ' || rec_seq.sequence_name || ' increment by ' ||
                   l_start_num;
    EXECUTE IMMEDIATE l_sql;
    EXECUTE IMMEDIATE 'select ' || rec_seq.sequence_name || '.nextval from dual'
      INTO l_start_num;
    l_sql := 'alter sequence ' || rec_seq.sequence_name || ' increment by 1';
    EXECUTE IMMEDIATE l_sql;
    dbms_output.put_line('SEQ_NAME:'||rec_seq.sequence_name);
  END LOOP; --FOR rec_seq IN cur_sequence LOOP
  dbms_output.put_line('l_count:' || l_count);
END;