Pages

24 December 2007

A simple demonstration about how to comparing 2 Oracle Database Instance(or Schema) objects(such as tables)

I want to show, how to compare objects of 2
oracle instances or schemas. Below, i show indexes and referential
constraints. You can write for all other objects. There are some tools
or scripts to compare 2 schemas. But they compare by object names. For instance if an index is renamed, you can see that as if the index does not exist. I compare the objects by their structures:

--QUERY_001 --This query compares SOURCE_DBLINK and current database in terms of INDEXes. --Comparison is done only by INDEX_NAMEs. Renamed-indexes can not be found!!! --Returns INDEXes which exist in only SOURCE_DBLINKSELECT index_name, table_name FROM all_indexes@SOURCE_DBLINK md WHERE table_owner = 'DBUSER' AND NOT EXISTS (SELECT 1 FROM all_indexes db WHERE md.table_owner = db.table_owner AND md.index_name = db.index_name);--QUERY_002 --This query compares SOURCE_DBLINK and current database in terms of INDEXes. --Comparison is done by index structure. Renamed-indexes can be found.This is am enhancement of QUERY_001 --Returns INDEXes which exist in only SOURCE_DBLINKSELECT * FROM all_ind_columns@SOURCE_DBLINK md WHERE table_owner = 'DBUSER' AND index_owner = 'DBUSER' AND NOT EXISTS (SELECT 1 FROM all_ind_columns db WHERE md.table_owner = db.table_owner AND md.index_owner = db.index_owner AND md.table_name = db.table_name AND md.column_name = db.column_name AND md.column_position = db.column_position); --QUERY_003 --This query compares SOURCE_DBLINK and current database in terms of Referantial Constraints. --Comparison is done only by CONTRAINT_NAMEs. Renamed-ref-constraints can not be found!!! --Returns REF-CONTRAINTs which exist in only SOURCE_DBLINKSELECT constraint_name, table_name, FROM all_constraints@SOURCE_DBLINK md WHERE owner = 'DBUSER' AND constraint_type = 'R' AND NOT EXISTS (SELECT 1 FROM all_constraints db WHERE md.owner = db.owner AND md.constraint_type = db.constraint_type AND md.constraint_name = db.constraint_name); --QUERY_004 --This query compares SOURCE_DBLINK and current database in terms of Referantial Constraints. --Comparison is done by constraint structure. Renamed-ref-constraints can be found.This is am enhancement of QUERY_003 --Returns REF-CONTRAINTs which exist in only SOURCE_DBLINKSELECT * FROM all_cons_columns@SOURCE_DBLINK md WHERE owner = 'DBUSER' AND NOT EXISTS (SELECT 1 FROM all_cons_columns db WHERE md.owner = db.owner AND md.table_name = db.table_name AND md.column_name = db.column_name AND NVL(md.position, 0) = NVL(db.position, 0));