We plan to move a database from a database server with an old single byte character set to a new database server with multibyte UTF8 character set. Our plan is as follows:
import the structure of the database with data pump via database link and metadata_only option
update all varchar2 fields from byte length to char length
disable all triggers and foreign key constraints
import the contents of the tables with data pump via database link and data_only option
enable the triggers and constraints
As the database runs in a 24x7 environment we want to keep the downtime as short as possible. So my idea was, that the first three steps can be done while production is still running and the downtime has to begin only before we start with step number four. But the problem seem to be the sequences. The next value of a sequence has to match the contents of the tables and should be part of the data_only option. But that is not true if I am not mistaken.
Unfortunately it is not possible to create a script like
select 'alter sequence ' ||sequence_owner || '.' || sequence_name ||
'start with ' || to char(last_number + increment_by) || ';'
from sys.dba_sequences
where sequence_owner ...
on the source side and run it on the target side as alter sequence does not allow to change the start value and is also not possible to start data pump with parameters like
INCLUDE=SEQUENCE SEQUENCE_EXISTS_ACTION = replace
If we drop and recreate all the sequences we have to take care of grants to other schemas and invalidations of other objects. Is there a better and more elegant way to solve this problem? Are there any useful options in data pump or alter sequence, I haven’t seen yet?
The source database is 12.2 and the target database will be 19c.