Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

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

You Asked

Hello Techies,

Each time when we refresh our databases we faced lot of issues with the sequences in lot of schemas then we have to find them fix them all , i' m thinking about few options where we can fix all the sequences in one shot in the database. I believe we can do this with some pl SQL procedure can you suggest me best approach to fix all the sequences in databases in One Shot?

and Connor said...

A little PLSQL can do the trick

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>




Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database