Script to increment or decrement all sequences in a schema by n=1000 numbers

advertisements

_____________________________________________________________________________________________________________________

In normal application development or during the application testing phases, at least in some rare situations we would have come across to increment all the sequence in a schema by n numbers. Manually incrementing the values are bit tedious job. You can use below mentioned script to achieve this goal.

In this example I have two sequences in my schema and I wanted to increment both indexes by 1000 numbers.

SQL> select sequence_name from user_sequences;

SEQUENCE_NAME

------------------------------

SEQSCOTT

SEQ_EXAMPLE

SQL> select SEQSCOTT.currval,SEQSCOTT.nextval from dual;

   CURRVAL    NEXTVAL

---------- ----------

         1          1

SQL> select SEQ_EXAMPLE.currval,SEQ_EXAMPLE.nextval from dual;

   CURRVAL    NEXTVAL

---------- ----------

      2302       2302

declare

       type tabs is table of number index by pls_integer;

       c tabs;

    begin

       for counter in (select sequence_name n from user_sequences)

    loop

        execute immediate

       'select '||counter.n||'.nextval from dual connect by level<=1000'

        bulk collect into c;

    end loop;

end;

/

PL/SQL procedure successfully completed.

SQL> select SEQ_EXAMPLE.currval,SEQ_EXAMPLE.nextval from dual;

   CURRVAL    NEXTVAL

---------- ----------

      3303       3303

SQL>  select SEQSCOTT.currval,SEQSCOTT.nextval from dual;

   CURRVAL    NEXTVAL

---------- ----------

      1002       1002

You can change the value mentioned in RED color above as per your requirement.

_____________________________________________________________________________________________________________________

Website Stats