Oracle Sequence 同步更新

今天的DB发现了一个问题，就是所有的sequence不是最新的MAX ID，比较奇怪为什么通过Toad导出来的Sequence的Max值不是最新的，有可能是跟缓存有关，姑且不管他的原因，解决方法如下：

首先引用oracle同步sequence里面的同步更新方法：

 

create or replace procedure p_update_sequence(                   i_sequence_name in varchar2,                   i_table_name in varchar2,                   i_id_column in varchar2            ) is    v_sql varchar2(2000);    v_max_id number;    v_diff number;  begin    v_sql := 'select max(' || i_id_column || ') from ' || i_table_name;    execute immediate v_sql into v_max_id;    v_sql := 'select ' || v_max_id || ' - ' || i_sequence_name || '.nextval from dual';    execute immediate v_sql into v_diff;    if v_diff <= 0 then      return;    end if;    v_sql := 'alter sequence ' || i_sequence_name || ' increment by ' || v_diff;    execute immediate v_sql;    v_sql := 'select ' || i_sequence_name || '.nextval from dual';    execute immediate v_sql into v_diff;    v_sql := 'alter sequence ' || i_sequence_name || ' increment by 1';    execute immediate v_sql;  end p_update_wf_sequence;

 

然而，我需要同步的是数据库中的其中两个模块的信息，所以用重新写了一个，这个procedure对数据库的设计要有一定的规范。

CREATE OR REPLACE PROCEDURE p_update_sequence2 ASv_sql    VARCHAR2(2000);v_max_id NUMBER;v_diff   NUMBER;BEGINFOR x IN (SELECT t.sequence_name, t.table_name, t.column_nameFROM (SELECT a.NAME AS sequence_name, 'T' || substr(a.NAME, 2, length(a.NAME) - 4) AS table_name, substr(a.NAME, 6, length(a.NAME) - 5) AS column_nameFROM dba_dependencies a   WHERE a.owner IN ('UNIDEV', 'UNIADM') AND a.referenced_type = 'SEQUENCE' AND (a.NAME LIKE 'S/_FN%' ESCAPE  '/' OR a.NAME LIKE 'S/_CM%' ESCAPE '/')) t   WHERE EXISTS (SELECT a.table_nameFROM dba_col_comments a   WHERE a.owner = 'UNIADM' AND a.table_name = t.table_name AND a.column_name = t.column_name))LOOPv_sql := 'drop sequence ' || x.sequence_name;EXECUTE IMMEDIATE v_sql;v_sql := 'select max(' || x.column_name || ')+1 from ' || x.table_name;EXECUTE IMMEDIATE v_sqlINTO v_max_id;v_sql := 'CREATE SEQUENCE ' || x.sequence_name || '  START WITH ' || v_max_id || '  MAXVALUE 999999999999999999999999999  MINVALUE 0  NOCYCLE  NOCACHE  NOORDER';EXECUTE IMMEDIATE v_sql;END LOOP;END p_update_sequence2;