oracle 11g迁移序列

背景：在一次因sysaux表空间数据文件损坏，归档缺失无法直接恢复，最终利用exp逻辑导出迁移业务数据。在迁移的过程中在序列迁移上遇到了一些问题，故写上序列迁移的正确过程。

1、查询源库序列当前值
set pagesize 6000
create_sequence.sql
	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') || ' ;'
from dba_sequences
where sequence_owner='ECOLOGY9'
--and cache_size != 0

备注：cache 0  的语句要替换为  nocache     //cache 0 语法错误

部分输出:
create sequence ECOLOGY9.TMPFNA_20210104105625_ID start with 1 maxvalue 9223372036854775807 minvalue 1 increment by 1 NOCACHE NOCYCLE NOORDER ;                                                                                                                                                                                                                                                                                                                                                                     
create sequence ECOLOGY9.FORMTABLE_MAIN_847_ID start with 1 maxvalue 9999999999999999999999999999 minvalue 1 increment by 1 NOCACHE NOCYCLE NOORDER ;                                                                                                                                                                                                                                                                                                                                                               
create sequence ECOLOGY9.FORMTABLE_MAIN_847_DT1_ID start with 1 maxvalue 9999999999999999999999999999 minvalue 1 increment by 1 NOCACHE NOCYCLE NOORDER ;                                                                                                                                                                                                                                                                                                                                                           
create sequence ECOLOGY9.FORMTABLE_MAIN_847_DT2_ID start with 1 maxvalue 9999999999999999999999999999 minvalue 1 increment by 1 NOCACHE NOCYCLE NOORDER ;                                                                                                                                                                                                                                                                                                                                                           
create sequence ECOLOGY9.FORMTABLE_MAIN_847_DT3_ID start with 1 maxvalue 9999999999999999999999999999 minvalue 1 increment by 1 NOCACHE NOCYCLE NOORDER ;                                                                                                                                                                                                                                                                                                                                                           
create sequence ECOLOGY9.FORMTABLE_MAIN_847_DT4_ID start with 1 maxvalue 9999999999999999999999999999 minvalue 1 increment by 1 NOCACHE NOCYCLE NOORDER ;                                                                                                                                                                                                                                                                                                                                                           
create sequence ECOLOGY9.FORMTABLE_MAIN_848_ID start with 7 maxvalue 9999999999999999999999999999 minvalue 1 increment by 1 NOCACHE NOCYCLE NOORDER ;                                                                                                                                                                                                                                                                                                                                                               
create sequence ECOLOGY9.FORMTABLE_MAIN_849_ID start with 124 maxvalue 9999999999999999999999999999 minvalue 1 increment by 1 NOCACHE NOCYCLE NOORDER ;                                                                                                                                                                                                                                                                                                                                                             
create sequence ECOLOGY9.FORMTABLE_MAIN_849_DT1_ID start with 173 maxvalue 9999999999999999999999999999 minvalue 1 increment by 1 NOCACHE NOCYCLE NOORDER ;                                                                                                                                                                                                                                                                                                                                                         
create sequence ECOLOGY9.FORMTABLE_MAIN_849_DT2_ID start with 294 maxvalue 9999999999999999999999999999 minvalue 1 increment by 1 NOCACHE NOCYCLE NOORDER ;                                                                                                                                                                                                                                                                                                                                                         
	create sequence ECOLOGY9.FORMTABLE_MAIN_850_ID start with 15 maxvalue 9999999999999999999999999999 minvalue 1 increment by 1 NOCACHE NOCYCLE NOORDER ;                                                                                                                                                                                                                                                                                                                                                              
create sequence ECOLOGY9.FORMTABLE_MAIN_850_DT1_ID start with 21 maxvalue 9999999999999999999999999999 minvalue 1 increment by 1 NOCACHE NOCYCLE NOORDER ;

2、目标库运行创建
@create_sequence.sql

3、应用验证
最终验证结果正常。
复制