Oracle 一次性修复所有序列

你好技术人员，

每次刷新数据库时，我们都会遇到很多模式中序列的问题，然后我们必须找到它们来修复所有这些问题，我正在考虑一些选项，我们可以在数据库中一枪修复所有序列。我相信我们可以用一些pl SQL过程来做到这一点，你能建议我最好的方法来一次修复数据库中的所有序列吗？

一点PLSQL可以做到这一点

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>