不知道大家有没有遇到过Oracle数据库通过exp导出的备份文件,然后再通过imp恢复之后,经常有Sequence不匹配的问题,就是Sequence的值比表里面的max(id)大,所以导致恢复之后插入新纪录会报主键重复的错误。
在此同大家分享一个存储过程,可以在恢复数据库之后调用该存储过程把不匹配的Sequence追加到匹配,希望对大家有所帮助。
======================================================================================================create or replace procedure BalanceSEQ is
v_seqName varchar2(200);
v_tablename varchar2(200);
v_seqCount number := 0;
v_num number := 0;
v_maxID number := 0;
v_sqlStr varchar2(500);
begin begin
select count(object_name)
into v_seqCount
from user_objects
where object_type = 'SEQUENCE';
Exception
when others then
null;
end; declare
Cursor cur_seq is
select object_name,
substr(object_name, 1, (instr(object_name, '_') - 1)) as table_name
from user_objects
where object_type = 'SEQUENCE'
and object_name <> 'KLINEOFOpt_SEQ';
begin
open cur_seq;
loop
fetch cur_seq
into v_seqName, v_tablename;
begin
v_sqlStr := 'select max(id) from ' || v_tablename; execute immediate v_sqlStr
into v_maxID;
v_maxID := nvl(v_maxID, 0); loop
begin
v_sqlStr := 'select ' || v_seqName || '.nextval from dual';
execute immediate v_sqlStr
into v_num;
v_num := nvl(v_num, (v_maxID + 1));
exception
when others then
v_num := v_maxID + 1;
--dbms_output.put_line(v_sqlStr || ' execute exception');
null;
end;
exit when v_num > v_maxID;
end loop;
exception
when others then
--dbms_output.put_line(v_sqlStr || ' execute exception');
null;
end; exit when cur_seq%notfound; end loop; Exception
when others then
null;
if cur_seq%isopen then
close cur_seq;
end if;
end;end BalanceSEQ;
/
在此同大家分享一个存储过程,可以在恢复数据库之后调用该存储过程把不匹配的Sequence追加到匹配,希望对大家有所帮助。
======================================================================================================create or replace procedure BalanceSEQ is
v_seqName varchar2(200);
v_tablename varchar2(200);
v_seqCount number := 0;
v_num number := 0;
v_maxID number := 0;
v_sqlStr varchar2(500);
begin begin
select count(object_name)
into v_seqCount
from user_objects
where object_type = 'SEQUENCE';
Exception
when others then
null;
end; declare
Cursor cur_seq is
select object_name,
substr(object_name, 1, (instr(object_name, '_') - 1)) as table_name
from user_objects
where object_type = 'SEQUENCE'
and object_name <> 'KLINEOFOpt_SEQ';
begin
open cur_seq;
loop
fetch cur_seq
into v_seqName, v_tablename;
begin
v_sqlStr := 'select max(id) from ' || v_tablename; execute immediate v_sqlStr
into v_maxID;
v_maxID := nvl(v_maxID, 0); loop
begin
v_sqlStr := 'select ' || v_seqName || '.nextval from dual';
execute immediate v_sqlStr
into v_num;
v_num := nvl(v_num, (v_maxID + 1));
exception
when others then
v_num := v_maxID + 1;
--dbms_output.put_line(v_sqlStr || ' execute exception');
null;
end;
exit when v_num > v_maxID;
end loop;
exception
when others then
--dbms_output.put_line(v_sqlStr || ' execute exception');
null;
end; exit when cur_seq%notfound; end loop; Exception
when others then
null;
if cur_seq%isopen then
close cur_seq;
end if;
end;end BalanceSEQ;
/
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货