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)