Thanks for the question, Anurag.
Asked: May 13, 2020 - 3:10 am UTC
Last updated: May 13, 2020 - 3:31 am UTC
Version: Oracle 12c
Viewed 10K+ times! This question is
SQL> SQL> create sequence seq1 start with 10 increment by 1; Sequence created. SQL> select seq1.nextval from dual; NEXTVAL ---------- 10 1 row selected. SQL> SQL> create sequence seq2 start with 17 increment by 1; Sequence created. SQL> select seq2.nextval from dual; NEXTVAL ---------- 17 1 row selected. SQL> SQL> create sequence seq3 start with 56 increment by 1; Sequence created. SQL> select seq3.nextval from dual; NEXTVAL ---------- 56 1 row selected. SQL> SQL> create sequence seq4 start with 99 increment by 1; Sequence created. SQL> select seq4.nextval from dual; NEXTVAL ---------- 99 1 row selected. SQL> SQL> SQL> declare 2 nv int; 3 begin 4 for i in ( select sequence_name from user_sequences 5 ) 6 loop 7 execute immediate 'select '||i.sequence_name||'.nextval from dual' into nv; 8 execute immediate 'alter sequence '||i.sequence_name||' increment by -'||nv||' minvalue 0'; 9 execute immediate 'select '||i.sequence_name||'.nextval from dual' into nv; 10 execute immediate 'alter sequence '||i.sequence_name||' increment by 1'; 11 end loop; 12 end; 13 / PL/SQL procedure successfully completed. SQL> SQL> select seq1.nextval from dual; NEXTVAL ---------- 1 1 row selected. SQL> select seq2.nextval from dual; NEXTVAL ---------- 1 1 row selected. SQL> select seq3.nextval from dual; NEXTVAL ---------- 1 1 row selected. SQL> select seq4.nextval from dual; NEXTVAL ---------- 1 1 row selected. SQL>
Need more information on Administration? Check out the Administrators guide for the Oracle Database