Reset Oracle Sequence in-place

HOW TO RESET A SEQUENCE IN-PLACE

  This avoids drop/create, and re-plumbing grants and synonyms.

  1. Set the "increment by" value to -nextval-1
  2. Increment the sequence
  3. Set the "increment by" value back to 1

SQL> select myschema.mysequence.nextval from dual;

   NEXTVAL

----------

1887203408

SQL> alter sequence myschema.mysequence increment by -1887203407;

Sequence altered.

SQL> select myschema.mysequence.nextval from dual;

   NEXTVAL

----------

         1

SQL> alter sequence myschema.mysequence increment by 1;

Sequence altered.

SQL> select myschema.mysequence.nextval from dual;

   NEXTVAL

----------

         2

SQL> alter system flush shared_pool;

System altered.