数据库比对工具

数据库比对工具是一款绿色小巧的数据库对比软件

功能：

该软件作用是将两个数据库(sql server)进行表结构比对，软件界面简洁，操作简单方便。

数据库对比方法：

alter session set sort_area_size=1024000000;

set serveroutput on;
declare
state1 varchar2(250);
state2 varchar2(250);
type cur_sor is ref cursor;
cur_1 cur_sor;
cur_2 cur_sor;
num1 number;
num2 number;
num3 number;
begin

--alter session set sort_area_size=1024000000;

delete from w_tab_cols;
delete from w_tab_only;
delete from w_tab_lob;

commit;

for rec in
(select distinct table_name from user_tab_cols@s_link where table_name in(select table_name from user_tab_cols@s_link where data_type like '%LOB%' or data_type like '%LONG%'))
loop
insert into w_tab_lob
values('s_link',rec.table_name);
end loop;

for rec in
(select distinct table_name from user_tab_cols@d_link where table_name in(select table_name from user_tab_cols@d_link where data_type like '%LOB%' or data_type like '%LONG%'))
loop
insert into w_tab_lob
values('d_link',rec.table_name);
end loop;

for rec in
(select table_name,column_name,data_type,data_length from user_tab_cols@s_link where table_name not in(select table_name from user_tab_cols@s_link where data_type like '%LOB%' or data_type like '%LONG%'))
loop
insert into w_tab_cols
values('s_link',rec.table_name,rec.column_name,rec.data_type,rec.data_length);
end loop;

for rec in
(select table_name,column_name,data_type,data_length from user_tab_cols@d_link where table_name not in(select table_name from user_tab_cols@d_link where data_type like '%LOB%' or data_type like '%LONG%'))
loop
insert into w_tab_cols
values('d_link',rec.table_name,rec.column_name,rec.data_type,rec.data_length);
end loop;

for rec in
(select table_name from w_tab_cols where side='s_link' and table_name not in
(select table_name from w_tab_cols where side='d_link'))
loop
insert into w_tab_only
values(rec.table_name,'s_link');
end loop;

for rec in
(select table_name from w_tab_cols where side='d_link' and table_name not in
(select table_name from w_tab_cols where side='s_link'))
loop
insert into w_tab_only
values(rec.table_name,'d_link');
end loop;



--

for rec in
(select distinct table_name from w_tab_cols
where table_name not in (select table_name from w_tab_only))
loop
num1:=0;
num2:=0;
num3:=0;

select count(*) into num1 from w_tab_cols where table_name=rec.table_name and side='s_link';
select count(*) into num2 from w_tab_cols where table_name=rec.table_name and side='d_link';
select count(*) into num3 from (select table_name,column_name,data_type,data_length from w_tab_cols where side='s_link' and table_name=rec.table_name intersect
select table_name,column_name,data_type,data_length from w_tab_cols where side='d_link' and table_name=rec.table_name);


if num1=num2 and num2=num3 and num3=num1
then
insert into w_tab_only
values(rec.table_name,'both');
end if;
end loop;


for rec in
(select distinct table_name from w_tab_cols where side='s_link' and table_name not in
(select table_name from w_tab_only))
loop
insert into w_tab_only
values(rec.table_name,'struct_diff');
end loop;

commit;

for rec in
(select table_name from w_tab_only where side='both' and table_name not like 'BIN$%')
loop

num1:=0;
num2:=0;
state1:='select count(*) from (select * from '||rec.table_name||'@s_link'||' minus '||'select * from '||rec.table_name||'@d_link)';
execute immediate state1 into num1;

state2:='select count(*) from (select * from '||rec.table_name||'@d_link'||' minus '||'select * from '||rec.table_name||'@s_link)';
execute immediate state2 into num2;

if num1>0 or num2>0
then
update w_tab_only set side='data_diff' where table_name=rec.table_name;
end if;
end loop;

commit;

end;
/