Recreate sequences with new starting numbers

The following script is useful in certain deployment scenarios.

For example, where the sequence names are created by CREATE SEQUENCE statements and the tables and columns, which the sequences are associated with have been imported with data.

A further scenario might be, data was loaded and the existing sequence, for whatever reason, was not used in the load process. The sequence would have to be dropped and recreated with a starting number that is in synch with the data loaded for the application to behave as per prior to the load.

In both of the above scenarios it might be useful to obtain the maximum number either from

1)the table column associated with the sequence
2)known gap between loaded data and last sequence number


set verify off
set feedback off
set head off

declare
  SEQ_NOT_EXIST   exception;
  TBL_NOT_EXIST   exception;
  pragma          exception_init(SEQ_NOT_EXIST, -2289);
  pragma          exception_init(TBL_NOT_EXIST, -942);
  l_Sequence_Name varchar2(30) := 'SQ_T1';
  l_Tbl_Name      varchar2(30) := 'T1';

begin

  begin

    execute immediate 'drop sequence ' || l_Sequence_Name;
    -- ignore if sequence does not exist, but 
    -- raise exception otherwise
    exception
      when SEQ_NOT_EXIST then
        null;
  end;

  begin
    execute immediate 'drop table ' || l_Tbl_Name;
    -- ignore if table does not exist, but 
    -- raise exception otherwise
    exception
      when TBL_NOT_EXIST then
        null;
  end;

end;
/

-- Create a table and sequence to test the script with
create table t1 (a number);
create sequence sq_t1;

set verify on
set feedback on
set head on

-- Insert some data using the sequence
insert into t1 values (sq_t1.nextval);
commit;

select * from t1;

set verify off
set feedback off
set head off

Drop and recreate sequence with starting number one more than the current value of the table/column that is associated with the sequence.

set serveroutput on
declare
  l_Sequence_Name varchar2(30) := 'SQ_T1';
  l_Tbl_Name      varchar2(30) := 'T1';
  l_Col_Name      varchar2(30) := 'A';
  l_Cnt           number;
  SEQ_NOT_EXIST   exception;
  pragma          exception_init(SEQ_NOT_EXIST, -2289);
begin

  execute immediate 'begin
                      select  max(' || l_Col_Name || ')'
                   || ' into :1 from '
                   || l_Tbl_Name || '; end;' using out l_Cnt;

  begin
    execute immediate 'drop sequence ' || l_Sequence_Name;
    exception
      when SEQ_NOT_EXIST then
        null; -- ignore if sequence does not exist, but raise exception in other cases
  end;

  begin

    execute immediate 'create sequence ' || l_Sequence_Name
                                         || ' start with '
                                         || to_char(l_Cnt + 1);

    dbms_output.put_line('Sequence ' || l_Sequence_Name
                                     || ' sucessfully recreated');
  end;
end;
/

set verify on
set feedback on
set head on

insert into t1 values (sq_t1.nextval);
commit;

select * from t1;

An alternative scenario would be to use the LAST_NUMBER column in the USER_SYNONYMS dictionary view and the if the gap(i.e. difference between max in data and LAST_NUMBER) is a known quantity, then simply specify as a parameter.

This might be useful for sequences which are not necessarily associated with a table column.
For example:

undefine GAP
set serveroutput on
declare
  l_Sequence_Name varchar2(30) := 'SQ_T1';
  l_Gap           number := to_number('&&GAP');
  l_Cnt           number;
  SEQ_NOT_EXIST   exception;
  pragma          exception_init(SEQ_NOT_EXIST, -2289);
begin

  begin
    select  last_number+l_Gap
    into    l_Cnt
    from    user_sequences
    where   sequence_name = l_Sequence_name;
  end;

  begin
    execute immediate 'drop sequence ' || l_Sequence_Name;

    -- ignore if sequence does not exist, but 
    -- raise exception in other cases    
    exception
      when SEQ_NOT_EXIST then
        null;
  end;

  execute immediate 'create sequence ' || l_Sequence_Name
                                       || ' start with '
                                       || to_char(l_Cnt);

  dbms_output.put_line('Sequence ' || l_Sequence_Name
                                   || ' sucessfully recreated');
end;
/