Monday, April 27, 2015

Change max value for Oracle Sequence

After data refresh/copy from one environment to another, usually developer realizes that they are using sequence and need to change the sequence's max value. To perform this I have developed this handy script:

This script assumes that the executor of this script has DBA (or DBA like privileges). Ability to select from DBA views and alter any sequence.

set serveroutput on

declare
   seq_owner  varchar2(30) := upper('&1');
   seq_name   varchar2(30) := upper('&2');
   desiredVal number := &3;
   oldIncrVal number;
   diff       number;
   newDiff    number;
   sqlStmt    varchar2(4000);
   currSeqVal number;
begin
   select INCREMENT_BY into oldIncrVal from dba_sequences where SEQUENCE_OWNER = seq_owner and SEQUENCE_NAME = seq_name;
   sqlStmt := 'select ' || seq_owner || '.' || seq_name || '.nextval from dual';
   dbms_output.put_line(sqlStmt);
   execute immediate sqlStmt into currSeqVal;
   diff := desiredVal - currSeqVal;
   dbms_output.put_line('alter sequence ' || seq_owner || '.' || seq_name || ' increment by ' || diff);
   execute immediate 'alter sequence ' || seq_owner || '.' || seq_name || ' increment by ' || diff;
   execute immediate sqlStmt into currSeqVal;
   newDiff := desiredVal - currSeqVal;
   dbms_output.put_line('alter sequence ' || seq_owner || '.' || seq_name || ' increment by ' || oldIncrVal);
   execute immediate 'alter sequence ' || seq_owner || '.' || seq_name || ' increment by ' || oldIncrVal;
end;
/


1 comment:

  1. You ought to be a part of a contest for one of the finest websites on the internet. I most certainly will highly recommend this web site!
    Database Performance Monitoring

    ReplyDelete