Oracle如何手动迁移sequence且防止sequence产生跳号

在做XTTS迁移时,发现某一特定sequence未迁移至目标环境,决定在目标环境,根据源端的DDL语句重建.

但客户要求,为防止sequence产生重号或跳号,在目标环境下的sequence start with值,需要与源端连续.

1.相关视图dba_sequences,特别关注字段increment_by

select * from dba_sequences where sequence_owner = 'HR' and sequence_name = 'MDRS_31C87C$';

2.原端数据库当前会话中,如何生成nextval和读取currval

[oravis@r12 ~]$ sqlplus hr/hr

SQL*Plus: Release 11.1.0.7.0 - Production on Sat May 1 12:49:56 2010

Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

HR@VIS> select MDRS_31C87C$.nextval from dual;
210
HR@VIS> select MDRS_31C87C$.currval from dual;
210

3.生成目标端创建sequence语句

--sequence with cache

select 'create sequence ' || sequence_owner || '.' || SEQUENCE_NAME ||
' start with ' || LAST_NUMBER || ' maxvalue ' || MAX_VALUE ||
' minvalue ' || MIN_VALUE || ' increment by ' || INCREMENT_BY ||
' cache ' || CACHE_SIZE || ' ' ||
decode(CYCLE_FLAG, 'N', 'NOCYCLE', 'CYCLE') || ' ' ||
decode(ORDER_FLAG, 'N', 'NOORDER', 'ORDER') || ' ;' "-- create seq scripts"
from dba_sequences
where cache_size != 0
and sequence_owner = 'HR'
and sequence_name = 'MDRS_31C87C$';
--注:为保持sequence在目标端的值跟原端连续,last_number需要修改为:在原端环境的CURRVAL-INCREMENT_BY

-sequence without cache
select 'create sequence ' || sequence_owner || '.' || sequence_name || ' start with ' || last_number ||
' maxvalue ' || max_value || ' minvalue ' || min_value ||
' increment by ' || increment_by || ' NOCACHE ' ||
decode(cycle_flag, 'N', 'NOCYCLE', 'CYCLE') || ' ' ||
decode(order_flag, 'N', 'NOORDER', 'ORDER') || ' ;' "-- create seq scripts"
from dba_sequences
where cache_size = 0
and sequence_owner = 'BEN'
and sequence_name = 'BEN_TCS_CAT_S';
--注:为保持sequence在目标端的值跟原端连续,last_number需要修改为:在原端环境的CURRVAL-INCREMENT_BY