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 AS
v_sql
VARCHAR2(2000);
v_max_id NUMBER;
v_diff
NUMBER;
BEGIN
FOR x IN (SELECT t.sequence_name,
t.table_name,
t.column_name
FROM (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_name
FROM 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_name
FROM dba_col_comments a
WHERE a.owner = 'UNIADM'
AND a.table_name = t.table_name
AND a.column_name = t.column_name))
LOOP
v_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_sql
INTO 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;

最后

以上就是踏实红酒为你收集整理的Oracle Sequence 同步更新的全部内容，希望文章能够帮你解决Oracle Sequence 同步更新所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。