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_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));

No comments: