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

1 comment:

Anonymous said...

You could certainly see your enthusiasm in the article you write.
The sector hopes for even more passionate writers such
as you who are not afraid to say how they believe.

At all times follow your heart.