Assuming connected to "test" database and want to compare to "product" via database link "prod".
Object difference:
select p.object_name prod_obj_name, p.object_type prod_obj_type, t.object_name test_obj_name, t.object_type test_obj_type
from user_objects@prod p
full outer join user_objects t
on p.object_name=t.object_name
and t.object_type=p.object_type
order by decode(p.object_name,NULL, 0, 1), decode(t.object_name,NULL, 0, 1),
p.object_name;
The results are shown in 3 sections: NULL first 2 columns - objects exist in "test" not in "prod"; NULL last 2 columns - objects exist in "prod" not in "test"; no NULL columns - objects exist in both. "Where" clause can be added to show only the difference instead of thousands of identical objects.
Table column difference:
WITH X as (select p.table_name, p.column_name
from user_tab_columns@prod p
join user_tables tt
on tt.table_name=p.table_name
),
Y as
(select t.table_name, t.column_name
from user_tab_columns t
join user_tables@prod pt
on pt.table_name=t.table_name
)
select x.table_name prod_table_name, x.column_name prod_column_name, y.table_name test_table_name, y.column_name test_column_name
from x full outer join y
on x.table_name=y.table_name
and x.column_name=y.column_name
where x.table_name is null or y.table_name is null
;
Similar to object difference, but it has to be done in a more complex query.
Index columns or even procedures etc can also be checked similarly if needed.
