1 序列对比
确认目标端的sequence的next_value大于等于源端的值，如不满足要求需要对目标端的sequence进行调整。
1.1 创建统计表。
源库和目标库上执行。
create table compare_sequence(
sequence_owner varchar2(32),
sequence_name varchar2(32),
next_value number
);
生成获取sequence下一个值的SQL语句。
select ‘insert into compare_sequence(sequence_owner,sequence_name,next_value) ‘||‘select ‘’’||sequence_owner||’’’ sequence_owner,’’’||sequence_name||’’’ sequence_name,’||sequence_owner||’.’||sequence_name||’.nextval from dual;’ from dba_sequences where sequence_owner in (‘USERNAME1’,’ USERNAME2’);
1.2 比较源端和目标端的sequence的next_value。
源库上执行。
select source.sequence_owner,
source.sequence_name,
source.next_value source_value,
target.next_value target_value
from goldengate.compare_sequencesource,
goldengate.compare_sequence@target_dblink target
where source.sequence_owner=target.sequence_owner
and source.sequence_name=target.sequence_name
and source.next_value > target.next_value
order by source.sequence_owner,source.sequence_name;
2 sequence不一致处理
如果出现目标端的sequence的next_value小于源端的值，不同情况有不同处理方式:
1）在目标端重建序列。
目标库上执行。
检查权限。
select ‘grant ’ || lower(p.privilege) || ’ on ’ || lower(p.owner) || ‘.’ || lower(p.table_name) || ’ to ’ || lower(p.grantee) || ‘;’ grant_sql
from dba_tab_privs p
where exists(select 1
from dba_sequences s
where s.sequence_owner in (‘USERNAME1’,’USERNAME2’)
and s.sequence_owner=p.owner
and s.sequence_name=p.table_name
)
order by p.owner,
p.table_name,
p.grantee,
p.privilege;
重建序列
select ‘drop sequence ‘||sequence_owner||’.’||sequence_name||’;’ dropseq
from dba_sequences
where sequence_owner=’ USERNAME1’;
col createseq for a300;
select ‘create sequence ‘||sequence_owner||’.’||sequence_name||
’ minvalue ‘||min_value||
’ maxvalue ‘||max_value||
’ start with ‘||last_number||
’ increment by ‘||increment_by||
(case when cache_size=0 then ’ nocache’ else ’ cache ‘||cache_size end) ||’;’ createseq
from dba_sequences where sequence_owner=’ USERNAME1’’;
按照检查权限的结果，进行授权。
2）手动刷新sequence
查询sequence：
select USERNAME1.seqtext.currval from dual;
刷新sequence
select USERNAME1.seqtext.nextval from dual;
批量刷新sequence
declare
temp number;
begin
for i in 1…10
loop
select USERNAME1.seqtext.nextval into temp from dual;
end loop;
end;
/
3）若出现差异比较大，使用increment by方式
- 找到这个sequence是哪张表所使用，开发知道，并告知哪一列使用该序列
2.找到这个表当前业务值的最大值
select max(KWH_AMT_ID) from sgpm.ARC_E_KWH_AMT
3.比较俩边sequence的差值
SQL> Select 1020203938-1012838392 from dual;
7365546
4. alter sequence SEQNAME increment by 7365546;
5.刷新一次序列select SEQNAME.nextval from dual;
6.再将序列的值修改回去alter sequence SEQNAME increment by 1;
3 出现ORA-08002报错：
查询sequence时，表示该sequence被占用，在可刷新的情况下手动刷新一次sequence便可使用currval查询。若不能做刷新动作，则通过dba_sequences视图结合CACHE_SIZE,LAST_NUMBER(LAST_NUMBER是每次缓存累计最大值，真实sequence一般小于此值）列做为参考查看：
select SEQUENCE_OWNER,SEQUENCE_NAME,CACHE_SIZE,LAST_NUMBER from dba_sequences where SEQUENCE_OWNER=’’ and SEQUENCE_NAME=’’;
dba_sequences视图解释参考官方说明“https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/ALL_SEQUENCES.html#GUID-5867804F-1339-4CB6-9A56-6B6DCECB61BB”
