Showing posts with label backward sequence jump. Show all posts
Showing posts with label backward sequence jump. Show all posts

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;
/