处理Maximo序列不一致的问题_maximo修改sequence-优快云博客

本文提供了一段SQL脚本，用于解决Maximo系统中序列与实际ID值不一致的问题。通过查询并调整序列值，确保其与数据表中的最大ID保持同步。

注：下面的脚本是在kevin网友的帖子上修改的，因为原帖脚本运行时有些小问题。

执行下面的sql可以同步所有Maximo序列与数据表中实际的ID值不一致的问题：

declare
    cursor seq_cursor is(select * from maxsequence);
    seq_cursor_row seq_cursor%rowtype;
    imaxseq        number(10);
    icurvalseq     number(10);
    strsql         varchar2(200);
    ic             number;
    isExist        number(10);
begin
    open seq_cursor;
    loop
        fetch seq_cursor into seq_cursor_row;
        EXIT WHEN seq_cursor%NOTFOUND;
        imaxseq := 0;
        strSQL := 'select count(*) from cols where column_name=:1 and table_name=:2';
        execute IMMEDIATE strSQL into isExist using seq_cursor_row.name, seq_cursor_row.tbname;
        dbms_output.put_line(isExist);

        if (isExist = 1) then
            strsql := 'select max(' || seq_cursor_row.name || ') from ' || seq_cursor_row.tbname;
            EXECUTE IMMEDIATE strsql into imaxseq;

strsql := 'select ' || seq_cursor_row.sequencename || '.nextval from dual';

EXECUTE IMMEDIATE strsql into icurvalseq;

            ic := imaxseq - icurvalseq;
            if ic > -1 then
                strSQL := 'alter sequence ' || seq_cursor_row.sequencename || ' increment by ' || ic || ' nocache';
                EXECUTE IMMEDIATE strsql;
                strSQL := 'select ' || seq_cursor_row.sequencename || '.nextval from dual';
                EXECUTE IMMEDIATE strsql into ic;
                strSQL := 'alter sequence ' || seq_cursor_row.sequencename || ' increment by 1 cache 20';

                EXECUTE IMMEDIATE strsql;
            end if;
        end if;

end loop;
close seq_cursor;
end;