从maximo7.1升级到7.5后,很多序列值没有同步更新,导致系统创建新条目时总是报序列错误。运行一下代码块即可自动修复序列值。
declare
v_sql varchar2(200);
v_maxid integer;
v_seqid integer;
cursor c_maxsequence is select * from maxsequence t1 where t1.tbname in (select t.table_name from user_tables t where t.num_rows>0) order by t1.tbname;
begin
for v_maxsequence in c_maxsequence loop
v_sql := 'select max(' || v_maxsequence.name || ') from ' || v_maxsequence.tbname;
execute immediate v_sql into v_maxid;
v_sql := 'select ' || v_maxsequence.sequencename || '.nextval from dual';
execute immediate v_sql into v_seqid;
if v_maxid>=v_seqid then
dbms_output.put_line('table=' || v_maxsequence.tbname || ', column=' || v_maxsequence.name || ', maxid=' || v_maxid || ', seqid=' || v_seqid);
v_sql := 'alter sequence ' || v_maxsequence.sequencename || ' increment by ' || (v_maxid-v_seqid+1);
dbms_output.put_line(v_sql);
execute immediate v_sql;
v_sql := 'select ' || v_maxsequence.sequencename || '.nextval from dual';
execute immediate v_sql into v_seqid;
v_sql := 'alter sequence ' || v_maxsequence.sequencename || ' increment by 1';
execute immediate v_sql;
end if;
end loop;
commit;
end;