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

_____________________________________________________________________________________________________________________

The Eucharistic Miracles of the World
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

0 comments:

Post a Comment

Labels

Oracle (578) General (60) Unix (47) Script (41) Blog (23) OCI (3) SQL* Loader (3) Datapump (2) Software (1)
DBA Tips
 

acehints.com Copyright 2011-21 All Rights Reserved | Site Map | Contact | Disclaimer