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_DBLINK
SELECT 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_DBLINK
SELECT *
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_DBLINK
SELECT 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_DBLINK
SELECT *
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));
--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_DBLINK
SELECT 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_DBLINK
SELECT *
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_DBLINK
SELECT 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_DBLINK
SELECT *
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));
