两个数据库建立一个链接 create database link dlyx_link CONNECT TO dlyx USING 'local';再作一个存储过程比较: create procedure pro as cursor t_sor is select * from ind a where not exists(select 1 from dlyx_link.ind b where a.INDEX_NAME=b.INDEX_NAME); begin ... end; / 大概是这种方式,比较分清谁有谁没
to chanet(牧师) _}}现在是希望先分析差异 ,再做数据库的同步! to beckhambobo(beckham) 你的方法正是我想要的,可以写的具体一些嘛,就是要对比主键,索引和约束的差异!!
create procedure pro as cursor t_sor_ind is select * from ind a where not exists(select 1 from ind@dlyx_link b where a.INDEX_NAME=b.INDEX_NAME); cursor t_sor_tab is select * from tab a where not exists(select 1 from tab@dlyx_link b where a.table_name=b.table_name); cursor t_sor_cols is select * from cols a where not exists(select 1 from cols@dlyx_link b where a.table_name=b.table_name); .... begin for v_sor in t_sor_ind loop dbms_output.put_line(v_sor.index_name); end loop; for v_sor in t_sor_tab loop ... end loop; for v_sor in t_sor_cols loop dbms_output.put_line(v_sor.column_name); end loop; end; /
create database link dlyx_link CONNECT TO dlyx USING 'local';再作一个存储过程比较:
create procedure pro
as
cursor t_sor is
select * from ind a where not exists(select 1 from dlyx_link.ind b where a.INDEX_NAME=b.INDEX_NAME);
begin
...
end;
/
大概是这种方式,比较分清谁有谁没
to beckhambobo(beckham) 你的方法正是我想要的,可以写的具体一些嘛,就是要对比主键,索引和约束的差异!!
user_tables,user_tab_columns,
user_constraints,user_cons_columns,
user_indexes,user_ind_columns写起来挺烦琐的,自己写喽
as
cursor t_sor_ind is
select * from ind a where not exists(select 1 from ind@dlyx_link b where a.INDEX_NAME=b.INDEX_NAME);
cursor t_sor_tab is
select * from tab a where not exists(select 1 from tab@dlyx_link b where a.table_name=b.table_name);
cursor t_sor_cols is
select * from cols a where not exists(select 1 from cols@dlyx_link b where a.table_name=b.table_name);
....
begin
for v_sor in t_sor_ind loop
dbms_output.put_line(v_sor.index_name);
end loop;
for v_sor in t_sor_tab loop
...
end loop;
for v_sor in t_sor_cols loop
dbms_output.put_line(v_sor.column_name);
end loop;
end;
/
如果要知道每个字段的类型、长度,索引内的每个字段的匹配、索引内字段的顺序等等
还要复杂好多