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

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.
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google