Alternate way of migrating sequences.

Problem Description

Often it becomes very difficult to manage sequences in the replicate environment, Below is a additional method provided to migrate sequences from SOURCE to TARGET replicate environment

Solution

Drop Sequences on TARGET & Recreate Sequences with NEXTVAL (on TARGET):

Build dynamic SQL to get sequences from SOURCE to build the dynamic SQL to RECREATE seqences on target database. 

Note this script prompts you to do one schema sequences  

NOTE BE CAREFUL.... RUN the script created drop_sequences_r.sql ONLY on TARGET

REM ******************************************************************

REM drop sequences on TARGET 

REM as they will need to be recreated at cutover. 

REM RUN on TARGET

REM Once application in shutdown on SOURCE and replication is STOPPED from source then recreate them on TARGET starting with last_value.

REM See   https://dbvisit.atlassian.net/wiki/display/ugd7/Sequences for advise

REM

REM SPOOL file name : drop_sequences_r.sql

SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON
SET HEADING OFF
Accept l_schema prompt "Enter Schema :"
spool drop_sequences_r.sql
select 'drop sequence '||sequence_owner||'.'||sequence_name||';'
from dba_sequences
where sequence_owner=upper('&l_schema') ;

spool off

exit

REM ******************************************************************

REM Run on SOURCE db

REM Create a table that will hold the sequences & the current value+1 then 

REM creates a dynamic sql to create sequences on TARGET

REM ******************************************************************
REM Create a table that will hold the sequences & the current value+1 then
REM creates a dynamic sql to create sequences on TARGET
REM ------------------------------------
REM ***** get_seq.sql****
REM ** Run connect as system on source db
REM ------------------------------------

drop table system.get_seq;

create table system.get_seq as
(select sequence_owner, sequence_name,last_number+1 start_val, increment_by,max_value,cache_size
from dba_sequences
where sequence_owner in upper('&l_schema'));

SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON

spool cr_sequence_r.sql

select 'create sequence '||sequence_owner||'.'|| sequence_name ||
' start with ' ||start_val||' increment by '||increment_by||
' maxvalue '||max_value||' '||' cache '||cache_size||';'
from system.get_seq
order by sequence_owner, sequence_name;

spool off

exit

SCP the file cr_sequence_r.sql to TARGET. 

RUN on TARGET SQL the scripts created,  drop_sequences_r.sql & cr_sequence_r.sql on TARGET to drop & create sequences for replicated users

RUN on TARGET SQL> @drop_sequences_r.sql

Run on TARGET SQL> @cr_sequence_r.sql 

 (note you may have to re-grant permissions to roles,etc on these sequences to users/roles)