Page Comparison

...

Select the sequences and their max numbers on the source database:

SQL> select sequence_owner, sequence_name, last_number from dba_sequences;

On Target

Reset the sequences use the following procedure:

create or replace

procedure Sequence_Reset( p_seq_name in varchar2, p_val in number default 0)

is

 l_current number := 0;

 l_difference number := 0;

 l_minvalue user_sequences.min_value%type := 0;

begin

select min_value

 into l_minvalue

 from user_sequences

 where sequence_name = p_seq_name;

execute immediate

 'select ' || p_seq_name || '.nextval from dual' INTO l_current;

if p_Val < l_minvalue then

 l_difference := l_minvalue - l_current;

 else

 l_difference := p_Val - l_current;

 end if;

if l_difference = 0 then

 return;

 end if;

execute immediate

 'alter sequence ' || p_seq_name || ' increment by ' || l_difference || 

 ' minvalue ' || l_minvalue;

execute immediate

 'select ' || p_seq_name || '.nextval from dual' INTO l_difference;

execute immediate

 'alter sequence ' || p_seq_name || ' increment by 1 minvalue ' || l_minvalue;

end Sequence_Reset;

Call the procedure for each sequence that needs to be reset, passing in the sequence name and the new value.

Example:

declare

begin

Sequence_reset('SEQ_REPLICATE_ID',25);

end;

/