Any Oracle schema Synchronization tool ?


- We have Schema A and  B

- They both are same schemas (kind of clones when we started)

- There are some changes done to schema A from past few months and none to B

- If we wanted to fresh schema B with all the changes done from A

  we can take export A and then drop B and then recreate B user and import all the data
  back.

 Other than above procedure is there any other utility which can do schema comparsion
  and run some scripts online and make sure both schemas A & B are synchronized.
OCUBEAsked:
Who is Participating?
 
sdstuberCommented:
Toad can do schema comparisons and generate a script of differences.

You could also extract objects with dbms_metadata from both sides and use any textbased diff tool.

As for the synching itself,  you could use datapump   either expdp/impdp  or the pl/sql package  dbms_datapump.
0
 
OP_ZaharinCommented:
- you can also do a comparison by script however you need to create a dblink between the 2 databases. eg as follows:

> comparing tables between databaseA and databaseB:
select * from user_tables
MINUS
select * from user_tables@databaseB;

> comparing columns between 2 tables:
select  column_name, data_type, data_length  from user_tab_columns
       where table_name = 'ABC'
MINUS
select  column_name, data_type, data_length  from user_tab_columns@databaseB
       where table_name = 'ABC'
0
 
OP_ZaharinCommented:
- you can also use 3rd party tools such as TOYS and DBDIFF:

compare and synchronize oracle database schemas: http://www.impacttoys.com/

compare 2 oracle databases: http://www.dkgas.com/oradbdiff.htm

OP
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
OCUBEAuthor Commented:


  Both the schemas are in the same database ( on same physical server).
0
 
OP_ZaharinCommented:
- you can then query to all_tables and specify the schema name:
select * from all_tables where owner = 'schema1'
MINUS
select * from all_tables where owner = 'schema2'

- and query to all_tab_columns for column comparison:
select  column_name, data_type, data_length  from all_tab_columns
       where table_name = 'ABC' and owner = 'schema1'
MINUS
select  column_name, data_type, data_length  from all_tab_columns
       where table_name = 'ABC' and owner = schema2'

0
 
akramrykProject ManagerCommented:
Please your need to maintain two similar scemas. You may also use Materilized views replication or Oracle streams to keep both scemas sync in real time or periodically.
0
 
JacobfwCommented:
Two other tools that could be options for you:

http://www.dbtools.com.br/EN/dbmanagerpro/index.php
and
http://www.softtreetech.com/ (DB Tools for Oracle)
0
 
OCUBEAuthor Commented:

  Do these tools Synchronize DATA as well ?
0
 
sdstuberCommented:
Toad, datapump  

or just merge sql statement over db link
0
 
OCUBEAuthor Commented:

 How about the other 3rd party tools recommended by experts here ?

 Don't they sync the data as well ?
0
 
OP_ZaharinCommented:
hi ocube,
- you can go thru all the link suggested by the experts and see if any of those meets you requirement. as for sync the data, look into my suggestion in ID: 36928311.
0
 
OP_ZaharinCommented:
- sorry to fast, my suggested link only sync the schema. as for the data you can do dblink between the databases as suggested by the expert earlier and do an insert such as - INSERT INTO tableA b WHERE NOT EXISTS (SELECT * FROM tableA@dblinkname WHERE b.id = id)  

- or use the legacy EXP then IMP to load the data.
0
 
OCUBEAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.